Uber: Promo Abuse — One Device, Many Accounts, Same Campaign
New-rider promos can be farmed by creating several accounts on one phone.
- Difficulty: Medium
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #985
Problem statement
New-rider promos can be farmed by creating several accounts on one phone. The fraud team flags a phone when several accounts registered on it redeemed the same campaign.
Return every pair of device_id and campaign for which 3 or more different rider accounts on that device redeemed that campaign, with the number of such accounts as accounts. An account that redeemed the same campaign twice counts once. Order by accounts descending, then device_id, then campaign.
A practice scenario set at Uber: the tables and data are invented, and this is not a question from, or endorsed by, Uber.
Tables
Table: RiderAccount
| Column | Type |
|---|---|
| rider_id | int |
| device_id | varchar |
| signed_up_on | date |
Primary key: rider_id.
One row per rider account, with the device it was registered on.
Table: Redemption
| Column | Type |
|---|---|
| redemption_id | int |
| rider_id | int |
| campaign | varchar |
| redeemed_on | date |
Primary key: redemption_id.
One row per promo redemption; rider_id always names an account.
Examples
Example 1
RiderAccount
| rider_id | device_id | signed_up_on |
|---|---|---|
| 1 | dev-a1f0 | 2024-03-01 |
| 2 | dev-a1f0 | 2024-03-02 |
| 3 | dev-a1f0 | 2024-03-02 |
| 4 | dev-b27c | 2024-03-05 |
| 5 | dev-b27c | 2024-03-06 |
| 6 | dev-c3d9 | 2024-03-07 |
| 7 | dev-b27c | 2024-03-09 |
Redemption
| redemption_id | rider_id | campaign | redeemed_on |
|---|---|---|---|
| 1 | 1 | FIRSTRIDE | 2024-03-01 |
| 2 | 2 | FIRSTRIDE | 2024-03-02 |
| 3 | 3 | FIRSTRIDE | 2024-03-03 |
| 4 | 3 | FIRSTRIDE | 2024-03-10 |
| 5 | 1 | WEEKEND20 | 2024-03-09 |
| 6 | 2 | WEEKEND20 | 2024-03-09 |
| 7 | 4 | FIRSTRIDE | 2024-03-05 |
| 8 | 5 | FIRSTRIDE | 2024-03-06 |
| 9 | 6 | FIRSTRIDE | 2024-03-07 |
| 10 | 7 | FIRSTRIDE | 2024-03-09 |
Output
| device_id | campaign | accounts |
|---|---|---|
| dev-a1f0 | FIRSTRIDE | 3 |
| dev-b27c | FIRSTRIDE | 3 |
How to solve Uber: Promo Abuse — One Device, Many Accounts, Same Campaign
The signal combines facts from two tables — the campaign from Redemption, the device from RiderAccount — so the query joins them on rider_id. Each joined row says "this account on this device redeemed this campaign once".
The unit being judged is a (device, campaign) pair, so that is the GROUP BY. Within a group, the number that matters is how many different accounts took part, not how many redemptions there were: rider 3 redeeming FIRSTRIDE twice is still one account. COUNT(DISTINCT r.rider_id) counts accounts, and HAVING keeps groups with at least three. A plain COUNT(*) would wrongly flag a device where two accounts redeemed four times.
The alternative removes duplicate (device, campaign, account) triples with SELECT DISTINCT in a derived table and then counts rows — the same thing in two steps. Three sort keys (accounts, device, campaign) make the order total, since two pairs can tie on the count. The cost is one join and one aggregation.
Reference solution (MySQL)
SELECT a.device_id, r.campaign, COUNT(DISTINCT r.rider_id) AS accounts
FROM Redemption r
JOIN RiderAccount a ON a.rider_id = r.rider_id
GROUP BY a.device_id, r.campaign
HAVING COUNT(DISTINCT r.rider_id) >= 3
ORDER BY accounts DESC, a.device_id, r.campaignAnother way
SELECT device_id, campaign, COUNT(*) AS accounts
FROM (SELECT DISTINCT a.device_id, r.campaign, r.rider_id
FROM Redemption r JOIN RiderAccount a ON a.rider_id = r.rider_id) x
GROUP BY device_id, campaign
HAVING COUNT(*) >= 3
ORDER BY accounts DESC, device_id, campaign← Uber: Drivers Gone Quiet Before the Festive Rush · Uber: Airport Queue Position When Each Driver Arrived →