Solve three SQL problems (easy/medium/hard)

Answer the following SQL tasks of increasing difficulty:

1. Aggregation: Given Orders(order_id, customer_id, amount, order_date), return each customer's total spend and order count for Jan 1–Dec 31, 2024, sorted by total spend descending.
2. Windowing: Given Transactions(id, user_id, amount, created_at), compute for each user the 7-day rolling sum of amount for every calendar date present in the data, emitting dates with zero activity where needed.
3. Advanced joins and gaps-islands: Given Sessions(user_id, start_time, end_time) and Memberships(user_id, plan, activated_at, canceled_at), find users who had any session time while their membership was inactive (outside [activated_at, canceled_at)), and output user_id and total inactive-session minutes.

Customer aggregation: total spend and order count in 2024

Given an Orders table with (order_id, customer_id, amount, order_date), return each customer's total spend and order count for orders placed from 2024-01-01 through 2024-12-31 (inclusive). Output columns: customer_id, total_spend, order_count. Sort by total_spend descending, then customer_id ascending.

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
order_date	DATE	NOT NULL

Sample data:

amount	customer_id	order_date	order_id
50	101	2024-01-10	1
25	101	2024-03-05	2
100	102	2024-02-14	3
75	103	2024-12-31	4
25	103	2024-07-04	5
40	102	2023-12-31	6
60	101	2025-01-01	7

Expected output

One row per customer who has at least one order in calendar year 2024, showing total spend and order count for that year.

customer_id	order_count	total_spend
102	1	100
103	2	100
101	2	75

Hints

1. Filter by the 2024 date range before aggregating.
2. Use SUM(amount) and COUNT(*) grouped by customer_id.

7-day rolling sum per user with zero-activity dates

Given Transactions(id, user_id, amount, created_at), compute for each user the 7-day rolling sum of amount for every calendar date between the minimum and maximum transaction dates in the table (inclusive). You must emit a row for each (user_id, date) pair in that global date range, including dates where the user has zero transactions. The 7-day window is the current date plus the previous 6 calendar days. Output columns: user_id, activity_date, rolling_7d_amount. Order by user_id, activity_date.

Assume rolling sums are based on daily totals (i.e., sum amounts per user per day first, then apply the rolling window).

Table transactions

column	type	constraints
id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
created_at	TIMESTAMP	NOT NULL

Sample data:

amount	created_at	id	user_id
10	2025-05-20 10:00:00	1	1
5	2025-05-22 12:00:00	2	1
7	2025-05-25 09:00:00	3	1
3	2025-05-21 08:00:00	4	2
4	2025-05-24 15:00:00	5	2
2	2025-05-24 18:00:00	6	2

Expected output

For each user and each calendar date in the global date range (2025-05-20 to 2025-05-25), show the rolling 7-day sum of daily amounts (including the current date). Dates with no user activity must appear with daily total = 0 and still contribute to the rolling window.

activity_date	rolling_7d_amount	user_id
2025-05-20	10	1
2025-05-21	10	1
2025-05-22	15	1
2025-05-23	15	1
2025-05-24	15	1
2025-05-25	22	1
2025-05-20	0	2
2025-05-21	3	2
2025-05-22	3	2
2025-05-23	3	2
2025-05-24	9	2
2025-05-25	9	2

Hints

1. Generate a calendar from MIN(created_at::date) to MAX(created_at::date).
2. Cross join users to the calendar to force zero-activity dates, then left join daily totals.
3. Use a window SUM with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.

Inactive membership session minutes (joins + time overlap)

Given Sessions(user_id, start_time, end_time) and Memberships(user_id, plan, activated_at, canceled_at), find users who had any session time while their membership was inactive.

A membership is active on the half-open interval [activated_at, canceled_at). If canceled_at is NULL, treat the membership as active indefinitely into the future.

For each user, compute the total number of minutes spent in sessions during inactive time (i.e., session time not covered by any active membership interval). Output columns: user_id, inactive_session_minutes.

Assume memberships for a given user do not overlap each other in time. Sessions may partially overlap membership windows.

Order by user_id ascending.

Table sessions

column	type	constraints
session_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
start_time	TIMESTAMP	NOT NULL
end_time	TIMESTAMP	NOT NULL

Sample data:

end_time	session_id	start_time	user_id
2025-02-15 11:00:00	1	2025-02-15 10:00:00	1
2025-03-10 09:30:00	2	2025-03-10 09:00:00	1
2025-01-10 10:45:00	3	2025-01-10 10:00:00	2
2025-04-01 00:30:00	4	2025-03-31 23:30:00	2
2025-05-15 13:00:00	5	2025-05-15 12:00:00	3
2025-05-25 12:20:00	6	2025-05-25 12:00:00	3

Table memberships

column	type	constraints
membership_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
plan	VARCHAR(20)	NOT NULL
activated_at	TIMESTAMP	NOT NULL
canceled_at	TIMESTAMP

Sample data:

activated_at	canceled_at	membership_id	plan	user_id
2025-01-01 00:00:00	2025-02-01 00:00:00	1	basic	1
2025-03-01 00:00:00	NULL	2	pro	1
2025-01-15 00:00:00	2025-04-01 00:00:00	3	basic	2
2025-05-01 00:00:00	2025-05-20 00:00:00	4	basic	3

Expected output

One row per user who has > 0 minutes of session time outside all active membership intervals, with total inactive minutes summed across all sessions (including partial overlaps).

inactive_session_minutes	user_id
60	1
75	2
20	3

Hints

1. Compute session duration, then subtract the overlapped time with membership intervals.
2. Use GREATEST(start_time, activated_at) and LEAST(end_time, canceled_at) to compute overlap.
3. Remember the membership interval is half-open: [activated_at, canceled_at), and treat NULL canceled_at as 'infinite'.