Uber: Promo Abuse — One Device, Many Accounts, Same Campaign

Medium
985.

Uber: Promo Abuse — One Device, Many Accounts, Same Campaign

MediumJoinsAggregation

MySQL

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

ColumnType
rider_idint
device_idvarchar
signed_up_ondate

Primary key: rider_id.

One row per rider account, with the device it was registered on.

Table: Redemption

ColumnType
redemption_idint
rider_idint
campaignvarchar
redeemed_ondate

Primary key: redemption_id.

One row per promo redemption; rider_id always names an account.

Example

The rows must come back in the order shown.

RiderAccount

rider_iddevice_idsigned_up_on
1dev-a1f02024-03-01
2dev-a1f02024-03-02
3dev-a1f02024-03-02
4dev-b27c2024-03-05
5dev-b27c2024-03-06
6dev-c3d92024-03-07
7dev-b27c2024-03-09

Redemption

redemption_idrider_idcampaignredeemed_on
11FIRSTRIDE2024-03-01
22FIRSTRIDE2024-03-02
33FIRSTRIDE2024-03-03
43FIRSTRIDE2024-03-10
51WEEKEND202024-03-09
62WEEKEND202024-03-09
74FIRSTRIDE2024-03-05
85FIRSTRIDE2024-03-06
96FIRSTRIDE2024-03-07
107FIRSTRIDE2024-03-09

Output

device_idcampaignaccounts
dev-a1f0FIRSTRIDE3
dev-b27cFIRSTRIDE3
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
rider_idint
device_idvarchar
signed_up_ondate

Primary key: rider_id.

One row per rider account, with the device it was registered on.

Table: Redemption

ColumnType
redemption_idint
rider_idint
campaignvarchar
redeemed_ondate

Primary key: redemption_id.

One row per promo redemption; rider_id always names an account.

Examples

Example 1

RiderAccount

rider_iddevice_idsigned_up_on
1dev-a1f02024-03-01
2dev-a1f02024-03-02
3dev-a1f02024-03-02
4dev-b27c2024-03-05
5dev-b27c2024-03-06
6dev-c3d92024-03-07
7dev-b27c2024-03-09

Redemption

redemption_idrider_idcampaignredeemed_on
11FIRSTRIDE2024-03-01
22FIRSTRIDE2024-03-02
33FIRSTRIDE2024-03-03
43FIRSTRIDE2024-03-10
51WEEKEND202024-03-09
62WEEKEND202024-03-09
74FIRSTRIDE2024-03-05
85FIRSTRIDE2024-03-06
96FIRSTRIDE2024-03-07
107FIRSTRIDE2024-03-09

Output

device_idcampaignaccounts
dev-a1f0FIRSTRIDE3
dev-b27cFIRSTRIDE3

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.campaign

Another 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 →