Solve three SQL problems (easy/medium/hard)
Company: OPPO
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates proficiency in SQL data manipulation including aggregation, window functions for rolling calculations, temporal joins, and gaps-and-islands analysis across increasing difficulty levels.
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.
Tables
orders(order_id INT, customer_id INT, amount DECIMAL(10,2), order_date DATE)
Hints
- Filter by the 2024 date range before aggregating.
- 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).
Tables
transactions(id INT, user_id INT, amount DECIMAL(10,2), created_at TIMESTAMP)
Hints
- Generate a calendar from MIN(created_at::date) to MAX(created_at::date).
- Cross join users to the calendar to force zero-activity dates, then left join daily totals.
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.
Tables
sessions(session_id INT, user_id INT, start_time TIMESTAMP, end_time TIMESTAMP)
memberships(membership_id INT, user_id INT, plan VARCHAR(20), activated_at TIMESTAMP, canceled_at TIMESTAMP)
Hints
- Compute session duration, then subtract the overlapped time with membership intervals.
- Use GREATEST(start_time, activated_at) and LEAST(end_time, canceled_at) to compute overlap.