I took this SQL test during a job interview. The instructions are (spelling mistake is theirs): “We’d like a data model for quick lookup per user. Create a query that returns 1 row per user containing information on their first purchance and their last.”
We have a table that looks like this - the table is called subscriptions which can also be considered transactions.
Table "public.subscriptions"
Column | Type | Collation | Nullable | Default
----------------+-----------------------------+-----------+----------+---------
transaction_id | character(36) | | not null |
purchased_at | timestamp without time zone | | not null |
purchase_price | double precision | | not null |
user_id | character varying(255) | | not null |
The way I did this was with two sub queries. One sub query ranked each user’s transactions. The other sub query counted total transactions per user - use this number to know which transaction was their last. The query with total transactions per user has one row for every user, so in the main query start with that, then join to the ranking query twice, once for the first transaction and once for the last.
with total_subs as
(
select user_id, count(distinct transaction_id) total_subs
from public.subscriptions
group by 1
),
first_and_last as
(
select user_id, transaction_id, purchased_at , purchase_price
,row_number() OVER(PARTITION BY user_id order by purchased_at asc ) as sub_asc_rn
from public.subscriptions
)
select a.user_id, b.transaction_id first_tran, b.purchased_at first_date , b.purchase_price first_price
, c.transaction_id last_tran, c.purchased_at last_date , c.purchase_price last_price
, a.total_subs
from total_subs a
join first_and_last b on a.user_id = b.user_id and b.sub_asc_rn = 1
join first_and_last c on a.user_id = c.user_id and c.sub_asc_rn = a.total_subs
;
That’s it. Below is what sample data in the public.subscriptions table looks like and below that is results of the initial inquiry.
-[ RECORD 1 ]--+-------------------------------------
transaction_id | 62a04c1e-4658-4908-8383-bd32123a1bf2
purchased_at | 2024-01-28 00:00:00
purchase_price | 27
user_id | Angela Kennedy
-[ RECORD 2 ]--+-------------------------------------
transaction_id | 9ffbc832-8232-4d11-af18-387c53d4437b
purchased_at | 2023-11-16 00:00:00
purchase_price | 14
user_id | Brittany Schneider
-[ RECORD 3 ]--+-------------------------------------
transaction_id | 512c4c53-22c4-4172-8e28-131b677f546f
purchased_at | 2023-03-19 00:00:00
purchase_price | 17
user_id | Tracy Parker
-[ RECORD 4 ]--+-------------------------------------
transaction_id | 42cfb233-8573-4cfb-ae71-a880c5d94bfc
purchased_at | 2023-12-23 00:00:00
purchase_price | 12
user_id | Heather Kelly
-[ RECORD 5 ]--+-------------------------------------
transaction_id | ee7d7f9d-0370-4755-a7d6-6a43fcdcd532
purchased_at | 2022-01-15 00:00:00
purchase_price | 28
user_id | Theresa Lowery
-[ RECORD 6 ]--+-------------------------------------
transaction_id | 9ab14c6f-28fe-4e08-9cf2-7240d25e1ea4
purchased_at | 2021-07-26 00:00:00
purchase_price | 15
user_id | James Robinson
-[ RECORD 7 ]--+-------------------------------------
transaction_id | a099865b-2066-4d88-90c8-bc93ec195da2
purchased_at | 2022-11-10 00:00:00
purchase_price | 19
user_id | Jared Rose
-[ RECORD 8 ]--+-------------------------------------
transaction_id | f0e5bf12-1afb-473c-9916-80dba4130d36
purchased_at | 2023-03-18 00:00:00
purchase_price | 23
user_id | Renee Sloan
-[ RECORD 9 ]--+-------------------------------------
transaction_id | 36693665-c11e-4955-aac3-f0d9b0a89846
purchased_at | 2022-04-15 00:00:00
purchase_price | 19
user_id | Christopher Ramirez
-[ RECORD 10 ]-+-------------------------------------
transaction_id | 626822b1-c14c-4d58-a023-0ff6d57f9d8c
purchased_at | 2023-02-04 00:00:00
purchase_price | 21
user_id | Mary Robles
|user_id|first_tran|first_date|first_price|last_tran|last_date|last_price|total_subs|
|-------|----------|----------|-----------|---------|---------|----------|----------|
|Aaron Bell|3cbf8e82-4e61-4d77-9b28-7d97bd8c7804|2019-08-24 17:20:13.846|638.27|3cbf8e82-4e61-4d77-9b28-7d97bd8c7804|2019-08-24 17:20:13.846|638.27|1|
|Alexis Simmons|8bc8e04c-6c6a-4469-80fc-babdde4b3321|2022-12-29 08:27:27.761|208.51|8bc8e04c-6c6a-4469-80fc-babdde4b3321|2022-12-29 08:27:27.761|208.51|1|
|Alice Johnson|c0f38ba8-4ecb-48aa-8a88-60dbd46aca8b|2020-04-16 00:32:17.004|222.41|b74ae3d0-f6a3-46c7-8318-f9958e2a31d2|2020-08-12 05:56:11.781|159.12|8|
|Alice Smith|8abf8350-5316-4e48-ac86-5a6799a2288d|2024-04-16 02:03:54.619|138.52|8abf8350-5316-4e48-ac86-5a6799a2288d|2024-04-16 02:03:54.619|138.52|1|
|Allison Howard|57e73d80-1446-4a0a-ac86-77c3409ddc03|2023-08-03 13:43:44.738|32.68|57e73d80-1446-4a0a-ac86-77c3409ddc03|2023-08-03 13:43:44.738|32.68|1|
|Alyssa Morris|e5d0e3e2-630d-42ea-94ab-5cf289b14f72|2019-08-05 09:27:13.570|800.61|e5d0e3e2-630d-42ea-94ab-5cf289b14f72|2019-08-05 09:27:13.570|800.61|1|
|Amanda Walker|0b623311-ae1b-464b-a82e-d2745a09bc03|2022-10-17 07:49:21.555|110.24|ca7d56da-aa79-4a91-9843-b1268c2f75fd|2022-11-29 05:28:24.722|159.67|8|
|Andrew Perez|74472909-05f2-47b7-bc11-644b7c81c173|2022-03-21 07:34:05.519|530.99|74472909-05f2-47b7-bc11-644b7c81c173|2022-03-21 07:34:05.519|530.99|1|