Three tables are on the bench: downloads (user_id, date, downloads), users (user_id, acc_id) and accounts (acc_id, paying_customer, which is yes or no). Find the dates on which non-paying customers downloaded more in total than paying customers. Return result with the columns date, non_paying and paying (each day's total downloads for that group), in date order.
The data
downloads22 rows
| user_idint64 | dateobject | downloadsint64 |
|---|
| 1 | 2025-02-01 | 4 |
| 2 | 2025-02-01 | 2 |
| 3 | 2025-02-01 | 6 |
| 4 | 2025-02-01 | 1 |
| 5 | 2025-02-01 | 3 |
| 6 | 2025-02-01 | 5 |
and 16 more rows
users6 rows
| user_idint64 | acc_idint64 |
|---|
| 1 | 101 |
| 2 | 101 |
| 3 | 102 |
| 4 | 103 |
| 5 | 104 |
| 6 | 104 |
accounts4 rows
| acc_idint64 | paying_customerobject |
|---|
| 101 | yes |
| 102 | no |
| 103 | yes |
| 104 | no |
Target output
| date | non_paying | paying |
|---|
| 2025-02-01 | 14 | 7 |
| 2025-02-03 | 6 | 3 |
| 2025-02-04 | 6 | 5 |
| 2025-02-05 | 3 | 0 |
What this question tests
Tests a three-table merge and a pivot from long to wide, then a comparison across the new columns. The trap is a date where only one side downloaded anything: after the pivot that side is missing, not zero, and a comparison with a missing value is never true.
More Python interview questions