Coding questions · Lyft
Query and transform marketplace data in SQL/Python
This question evaluates mastery of data manipulation and analytics skills in the Data Manipulation (SQL/Python) domain, including SQL aggregation and window functions, time-based joins and grouping, deduplication of status streams, handling late-arriving events, and implementing rolling metrics using both SQL and pandas.
Assume today is 2025-09-01. Use the following schema and sample data to answer the questions with both SQL (preferred) and equivalent Python (pandas) pseudocode. Treat timestamps as UTC.
Schema
- events(event_time TIMESTAMP, user_id INT, event_type ENUM(impression, click, add_to_cart, purchase), city STRING, session_id STRING)
- orders(order_id INT, user_id INT, created_at TIMESTAMP, status ENUM(placed, cancelled, completed), status_updated_at TIMESTAMP, city STRING, supplier_id INT)
- inventory(supplier_id INT, date DATE, units_available INT, city STRING)
Sample tables (tiny, illustrative) events event_time user_id event_type city session_id 2025-08-26 08:01:00 101 impression Austin s1 2025-08-26 08:02:10 101 click Austin s1 2025-08-26 09:15:00 102 impression Austin s2 2025-08-27 10:00:05 103 impression Boston s3 2025-08-27 10:05:10 103 purchase Boston s3 2025-08-28 12:00:00 104 impression Austin s4 2025-08-28 12:03:00 104 add_to_cart Austin s4 2025-08-29 07:45:00 105 impression Austin s5 2025-08-30 14:20:00 101 purchase Austin s6 2025-09-01 09:00:00 106 impression Boston s7
orders order_id user_id created_at status status_updated_at city supplier_id 5001 103 2025-08-27 10:04:59 completed 2025-08-27 10:05:20 Boston 900 5002 101 2025-08-30 14:19:50 placed 2025-08-30 14:19:50 Austin 901 5002 101 2025-08-30 14:19:50 cancelled 2025-08-30 14:25:00 Austin 901 5003 104 2025-08-28 12:02:30 placed 2025-08-28 12:02:30 Austin 902 5003 104 2025-08-28 12:02:30 completed 2025-08-28 12:10:00 Austin 902 5004 105 2025-08-29 07:50:00 placed 2025-08-29 07:50:00 Austin 901 5004 105 2025-08-29 07:50:00 cancelled 2025-08-29 08:05:00 Austin 901
inventory supplier_id date units_available city 900 2025-08-27 5 Boston 901 2025-08-29 1 Austin 901 2025-08-30 3 Austin 902 2025-08-28 2 Austin
Tasks
- City-day funnel: For each city and each date between 2025-08-26 and 2025-09-01 inclusive, compute: (i) unique users with at least one impression; (ii) completed orders (using the latest status per order_id as of its max status_updated_at); (iii) conversion rate = completed_orders / unique_impression_users; and (iv) a 7-day rolling conversion rate per city (window ending on that date).
- Supplier-day fill rate: For each supplier and date in the same window, compute demand = count of unique order_id whose created_at falls on that date and whose final status (latest by status_updated_at) is not cancelled. Then compute fill_rate = LEAST(demand, units_available) / NULLIF(demand, 0). Return supplier_id, date, demand, units_available, fill_rate; default units_available to 0 when missing.
- Late completions: Ensure that if an order is created on 2025-08-28 but completes on 2025-08-29, it contributes to demand on 2025-08-28 and to completed_orders on the date of completion only if you are computing completion-by-day; explain which definition you used for Task 1 and why.
- Provide SQL that is robust to duplicated rows in orders (multiple status lines per order_id) by selecting only the latest status per order_id via window functions. Then sketch equivalent pandas code, including how you would compute the rolling metric and handle division-by-zero safely.
City-Day Conversion Funnel with 7-Day Rolling Rate
Using the tables defined below, write a PostgreSQL query that returns a city-level conversion funnel by day.
For each city and each calendar date from 2025-08-26 through 2025-09-01, inclusive, compute:
- unique_impression_users: distinct users who generated at least one 'impression' event in that city on that date
- completed_orders: orders whose final status is 'completed' and whose completion date, defined as DATE of the latest status_updated_at for that order_id, equals that calendar date in that city
- conversion_rate: completed_orders / unique_impression_users as a decimal; return NULL when unique_impression_users = 0
- rolling_7d_conversion_rate: for each city and date, sum(completed_orders) over that date and the previous 6 rows in the same city divided by sum(unique_impression_users) over the same window; return NULL when the rolling denominator is 0
Additional requirements:
- The orders table can have multiple rows per order_id. Use only the latest row per order_id by status_updated_at to determine final status, city, supplier, and completion time.
- Treat timestamps as UTC and derive dates with DATE(timestamp_column).
- Include every combination of city and calendar date in the requested window for cities appearing in either events or orders.
Return: city, metric_date, unique_impression_users, completed_orders, conversion_rate, rolling_7d_conversion_rate. Order by city, then metric_date.
Table events
| column | type | constraints |
|---|---|---|
| event_time | TIMESTAMP | NOT NULL |
| user_id | INT | NOT NULL |
| event_type | VARCHAR(20) | NOT NULL |
| city | VARCHAR(50) | NOT NULL |
| session_id | VARCHAR(50) | NOT NULL |
Sample data:
| city | event_time | event_type | session_id | user_id |
|---|---|---|---|---|
| Austin | 2025-08-26 08:01:00 | impression | s1 | 101 |
| Austin | 2025-08-26 08:02:10 | click | s1 | 101 |
| Austin | 2025-08-26 09:15:00 | impression | s2 | 102 |
| Boston | 2025-08-27 10:00:05 | impression | s3 | 103 |
| Boston | 2025-08-27 10:05:10 | purchase | s3 | 103 |
| Austin | 2025-08-28 12:00:00 | impression | s4 | 104 |
| Austin | 2025-08-28 12:03:00 | add_to_cart | s4 | 104 |
| Austin | 2025-08-29 07:45:00 | impression | s5 | 105 |
| Austin | 2025-08-30 14:20:00 | purchase | s6 | 101 |
| Boston | 2025-09-01 09:00:00 | impression | s7 | 106 |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
| status_updated_at | TIMESTAMP | NOT NULL |
| city | VARCHAR(50) | NOT NULL |
| supplier_id | INT | NOT NULL |
Sample data:
| city | created_at | order_id | status | status_updated_at | supplier_id | user_id |
|---|---|---|---|---|---|---|
| Boston | 2025-08-27 10:04:59 | 5001 | completed | 2025-08-27 10:05:20 | 900 | 103 |
| Austin | 2025-08-30 14:19:50 | 5002 | placed | 2025-08-30 14:19:50 | 901 | 101 |
| Austin | 2025-08-30 14:19:50 | 5002 | cancelled | 2025-08-30 14:25:00 | 901 | 101 |
| Austin | 2025-08-28 12:02:30 | 5003 | placed | 2025-08-28 12:02:30 | 902 | 104 |
| Austin | 2025-08-28 12:02:30 | 5003 | completed | 2025-08-28 12:10:00 | 902 | 104 |
| Austin | 2025-08-29 07:50:00 | 5004 | placed | 2025-08-29 07:50:00 | 901 | 105 |
| Austin | 2025-08-29 07:50:00 | 5004 | cancelled | 2025-08-29 08:05:00 | 901 | 105 |
Table inventory
| column | type | constraints |
|---|---|---|
| supplier_id | INT | NOT NULL |
| inventory_date | DATE | NOT NULL |
| units_available | INT | NOT NULL |
| city | VARCHAR(50) | NOT NULL |
Sample data:
| city | inventory_date | supplier_id | units_available |
|---|---|---|---|
| Boston | 2025-08-27 | 900 | 5 |
| Austin | 2025-08-29 | 901 | 1 |
| Austin | 2025-08-30 | 901 | 3 |
| Austin | 2025-08-28 | 902 | 2 |
Expected output
One row per (city, date) for each calendar date from 2025-08-26 to 2025-09-01 inclusive, showing daily unique impression users, completed orders (by completion date and final status), the daily conversion rate, and the 7-day rolling conversion rate per city. Conversion rates are NULL when the relevant denominator is zero.
| city | completed_orders | conversion_rate | metric_date | rolling_7d_conversion_rate | unique_impression_users |
|---|---|---|---|---|---|
| Austin | 0 | 0 | 2025-08-26 | 0 | 2 |
| Austin | 0 | NULL | 2025-08-27 | 0 | 0 |
| Austin | 1 | 1 | 2025-08-28 | 0.3333 | 1 |
| Austin | 0 | 0 | 2025-08-29 | 0.25 | 1 |
| Austin | 0 | NULL | 2025-08-30 | 0.25 | 0 |
| Austin | 0 | NULL | 2025-08-31 | 0.25 | 0 |
| Austin | 0 | NULL | 2025-09-01 | 0.25 | 0 |
| Boston | 0 | NULL | 2025-08-26 | NULL | 0 |
| Boston | 1 | 1 | 2025-08-27 | 1 | 1 |
| Boston | 0 | NULL | 2025-08-28 | 1 | 0 |
| Boston | 0 | NULL | 2025-08-29 | 1 | 0 |
| Boston | 0 | NULL | 2025-08-30 | 1 | 0 |
| Boston | 0 | NULL | 2025-08-31 | 1 | 0 |
| Boston | 0 | 0 | 2025-09-01 | 0.5 | 1 |
Hints
- Use ROW_NUMBER() over each order_id ordered by status_updated_at descending to find the final order row.
- Cross join a recursive date spine with the union of cities from events and orders before left joining daily aggregates.
- Compute rolling conversion from rolling sums, not from an average of daily conversion rates.
Supplier-Day Fill Rate from Orders and Inventory
Using the same marketplace tables, write a PostgreSQL query that computes supplier-day fill rate for the window from 2025-08-26 through 2025-09-01, inclusive.
For each supplier_id and each calendar date in that range, compute:
- demand: the count of distinct order_id whose created_at date equals that calendar date and whose final status, based on the latest status_updated_at row for that order_id, is not 'cancelled'
- units_available: the units_available value from inventory for that supplier_id and date; use 0 when no inventory row exists
- fill_rate: LEAST(demand, units_available) / demand; return NULL when demand = 0
Additional requirements:
- The orders table can have multiple rows per order_id. Use only the latest row per order_id by status_updated_at to determine final status and supplier_id.
- Include every combination of supplier_id and calendar date in the requested window for suppliers appearing in either orders or inventory.
Return: supplier_id, metric_date, demand, units_available, fill_rate. Order by supplier_id, then metric_date.
Table events
| column | type | constraints |
|---|---|---|
| event_time | TIMESTAMP | NOT NULL |
| user_id | INT | NOT NULL |
| event_type | VARCHAR(20) | NOT NULL |
| city | VARCHAR(50) | NOT NULL |
| session_id | VARCHAR(50) | NOT NULL |
Sample data:
| city | event_time | event_type | session_id | user_id |
|---|---|---|---|---|
| Austin | 2025-08-26 08:01:00 | impression | s1 | 101 |
| Austin | 2025-08-26 08:02:10 | click | s1 | 101 |
| Austin | 2025-08-26 09:15:00 | impression | s2 | 102 |
| Boston | 2025-08-27 10:00:05 | impression | s3 | 103 |
| Boston | 2025-08-27 10:05:10 | purchase | s3 | 103 |
| Austin | 2025-08-28 12:00:00 | impression | s4 | 104 |
| Austin | 2025-08-28 12:03:00 | add_to_cart | s4 | 104 |
| Austin | 2025-08-29 07:45:00 | impression | s5 | 105 |
| Austin | 2025-08-30 14:20:00 | purchase | s6 | 101 |
| Boston | 2025-09-01 09:00:00 | impression | s7 | 106 |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | NOT NULL |
| user_id | INT | NOT NULL |
| created_at | TIMESTAMP | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
| status_updated_at | TIMESTAMP | NOT NULL |
| city | VARCHAR(50) | NOT NULL |
| supplier_id | INT | NOT NULL |
Sample data:
| city | created_at | order_id | status | status_updated_at | supplier_id | user_id |
|---|---|---|---|---|---|---|
| Boston | 2025-08-27 10:04:59 | 5001 | completed | 2025-08-27 10:05:20 | 900 | 103 |
| Austin | 2025-08-30 14:19:50 | 5002 | placed | 2025-08-30 14:19:50 | 901 | 101 |
| Austin | 2025-08-30 14:19:50 | 5002 | cancelled | 2025-08-30 14:25:00 | 901 | 101 |
| Austin | 2025-08-28 12:02:30 | 5003 | placed | 2025-08-28 12:02:30 | 902 | 104 |
| Austin | 2025-08-28 12:02:30 | 5003 | completed | 2025-08-28 12:10:00 | 902 | 104 |
| Austin | 2025-08-29 07:50:00 | 5004 | placed | 2025-08-29 07:50:00 | 901 | 105 |
| Austin | 2025-08-29 07:50:00 | 5004 | cancelled | 2025-08-29 08:05:00 | 901 | 105 |
Table inventory
| column | type | constraints |
|---|---|---|
| supplier_id | INT | NOT NULL |
| inventory_date | DATE | NOT NULL |
| units_available | INT | NOT NULL |
| city | VARCHAR(50) | NOT NULL |
Sample data:
| city | inventory_date | supplier_id | units_available |
|---|---|---|---|
| Boston | 2025-08-27 | 900 | 5 |
| Austin | 2025-08-29 | 901 | 1 |
| Austin | 2025-08-30 | 901 | 3 |
| Austin | 2025-08-28 | 902 | 2 |
Expected output
One row per (supplier_id, date) for each calendar date from 2025-08-26 to 2025-09-01 inclusive that appears in either orders or inventory, showing demand (final-status non-cancelled orders by created_at date), units_available (defaulted to 0 when missing), and fill_rate = LEAST(demand, units_available) / demand, or NULL when demand = 0.
| demand | fill_rate | metric_date | supplier_id | units_available |
|---|---|---|---|---|
| 0 | NULL | 2025-08-26 | 900 | 0 |
| 1 | 1 | 2025-08-27 | 900 | 5 |
| 0 | NULL | 2025-08-28 | 900 | 0 |
| 0 | NULL | 2025-08-29 | 900 | 0 |
| 0 | NULL | 2025-08-30 | 900 | 0 |
| 0 | NULL | 2025-08-31 | 900 | 0 |
| 0 | NULL | 2025-09-01 | 900 | 0 |
| 0 | NULL | 2025-08-26 | 901 | 0 |
| 0 | NULL | 2025-08-27 | 901 | 0 |
| 0 | NULL | 2025-08-28 | 901 | 0 |
| 0 | NULL | 2025-08-29 | 901 | 1 |
| 0 | NULL | 2025-08-30 | 901 | 3 |
| 0 | NULL | 2025-08-31 | 901 | 0 |
| 0 | NULL | 2025-09-01 | 901 | 0 |
| 0 | NULL | 2025-08-26 | 902 | 0 |
| 0 | NULL | 2025-08-27 | 902 | 0 |
| 1 | 1 | 2025-08-28 | 902 | 2 |
| 0 | NULL | 2025-08-29 | 902 | 0 |
| 0 | NULL | 2025-08-30 | 902 | 0 |
| 0 | NULL | 2025-08-31 | 902 | 0 |
| 0 | NULL | 2025-09-01 | 902 | 0 |
Hints
- Collapse order history to one latest row per order_id before counting demand.
- Generate all supplier-date combinations first, then left join demand and inventory metrics.
- Use date + 1 in the recursive PostgreSQL calendar CTE and guard the fill-rate denominator.