Business Context
You’re on the data engineering on-call rotation for a high-volume e-commerce marketplace (millions of daily sessions, tens of thousands of orders/day). Product analytics relies on client-side funnel events (sessions and checkout success), while Finance relies on orders and captured payments. A recent mobile release caused intermittent tracking and payment issues, and leadership wants a daily defect monitor that quickly pinpoints whether the problem is in instrumentation, order creation, or payment capture.
Task
Write a SQL query that produces a daily report by device_type for the dates present in the data, combining funnel events, orders, and payments. The report should compute conversion and revenue deltas and flag potential defects.
Requirements
- Aggregate sessions as the count of distinct
session_id with event_name = 'session_start' per event_date and device_type.
- Aggregate checkouts as the count of distinct
session_id with event_name = 'checkout_success' per event_date and device_type.
- Aggregate orders and recorded_revenue from
orders per event_date = DATE(created_at) and device_type (infer device from funnel_events.session_id; if missing, use 'unknown').
- Aggregate paid_orders and captured_revenue from
payments for those orders where payments.status = 'captured'.
- Compute:
conversion_rate = checkouts / sessions (0 if sessions = 0)order_to_payment_gap = paid_orders - ordersrevenue_delta = captured_revenue - recorded_revenue
- Compute
defect_flag = 1 if either:
- day-over-day conversion drops by 20% or more for the same device (
conversion_rate <= 0.8 * prior_day_conversion_rate), OR
ABS(revenue_delta) > 50
Otherwise defect_flag = 0.
- Return rows ordered by
event_date, then device_type.
Table Definitions
funnel_events
| column | type | description |
|---|
| event_id | BIGINT | Primary key for each event |
| event_ts | TIMESTAMP | UTC timestamp of the event |
| session_id | VARCHAR(64) | Session identifier |
| user_id | BIGINT | User identifier |
| device_type | VARCHAR(16) | ios/android/web |
| event_name | VARCHAR(64) | Funnel event name |
orders
| column | type | description |
|---|
| order_id | BIGINT | Primary key for each order |
| created_at | TIMESTAMP | UTC order creation time |
| user_id | BIGINT | Purchasing user |
| session_id | VARCHAR(64) | Session that led to the order (nullable) |
| total_amount | NUMERIC(10,2) | Order total |
| currency | VARCHAR(3) | ISO currency code |
payments
| column | type | description |
|---|
| payment_id | BIGINT | Primary key for each payment attempt |
| order_id | BIGINT | Order being paid |
| captured_at | TIMESTAMP | Capture timestamp (nullable) |
| status | VARCHAR(16) | captured/failed/refunded/etc. |
| amount | NUMERIC(10,2) | Payment amount |
Sample Data
funnel_events
| event_id | event_ts | session_id | user_id | device_type | event_name |
|---|
| 101 | 2026-02-20 10:00:00 | s1 | 1 | ios | session_start |
| 102 | 2026-02-20 10:02:00 | s1 | 1 | ios | checkout_success |
| 103 | 2026-02-21 09:00:00 | s2 | 2 | ios | session_start |
| 104 | 2026-02-22 11:00:00 | s3 | 3 | ios | session_start |
| 105 | 2026-02-22 11:05:00 | s3 | 3 | ios | checkout_success |
orders
| order_id | created_at | user_id | session_id | total_amount | currency |
|---|
| 5001 | 2026-02-20 10:03:00 | 1 | s1 | 120.00 | USD |
| 5002 | 2026-02-21 09:10:00 | 2 | s2 | 80.00 | USD |
| 5003 | 2026-02-22 11:06:00 | 3 | s3 | 200.00 | USD |
payments
| payment_id | order_id | captured_at | status | amount |
|---|
| 9001 | 5001 | 2026-02-20 10:04:00 | captured | 120.00 |
| 9002 | 5002 | 2026-02-21 09:11:00 | failed | 80.00 |
| 9003 | 5003 | 2026-02-22 11:07:00 | captured | 260.00 |
Expected Output
| event_date | device_type | sessions | checkouts | conversion_rate | orders | paid_orders | order_to_payment_gap | recorded_revenue | captured_revenue | revenue_delta | defect_flag |
|---|
| 2026-02-20 | ios | 1 | 1 | 1.0 | 1 | 1 | 0 | 120.0 | 120.0 | 0.0 | 0 |
| 2026-02-21 | ios | 1 | 0 | 0.0 | 1 | 0 | -1 | 80.0 | 0.0 | -80.0 | 1 |
| 2026-02-22 | ios | 1 | 1 | 1.0 | 1 | 1 | 0 | 200.0 | 260.0 | 60.0 | 1 |
