Uber: Promo Codes Nobody Has Redeemed — SQL Joins Problem
Growth marketing is cleaning up the promo catalogue: a code that has been live for a while and was never used is retired.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #976
Problem statement
Growth marketing is cleaning up the promo catalogue: a code that has been live for a while and was never used is retired.
Return the promo_code and campaign of every code created before 2024-06-01 that has no redemption at all in PromoRedemption. Codes created on or after 2024-06-01 are too new to judge and are left out. Order the result by promo_code.
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: PromoCode
| Column | Type |
|---|---|
| promo_code | varchar |
| campaign | varchar |
| created_on | date |
Primary key: promo_code.
One row per promo code. Codes are upper-case.
Table: PromoRedemption
| Column | Type |
|---|---|
| redemption_id | int |
| promo_code | varchar |
| rider_id | int |
| redeemed_on | date |
Primary key: redemption_id.
One row each time a rider applied a code to a trip or order; promo_code always names an existing code.
Examples
Example 1
PromoCode
| promo_code | campaign | created_on |
|---|---|---|
| EATS100 | Eats launch | 2024-05-02 |
| MONSOON25 | Monsoon rides | 2024-05-20 |
| RIDE50 | New rider | 2024-04-11 |
| AIRPORT75 | Airport drops | 2024-05-28 |
| DIWALI40 | Festive | 2024-06-03 |
| CAMPUS30 | Campus | 2024-05-31 |
PromoRedemption
| redemption_id | promo_code | rider_id | redeemed_on |
|---|---|---|---|
| 1 | RIDE50 | 201 | 2024-04-20 |
| 2 | RIDE50 | 202 | 2024-05-01 |
| 3 | MONSOON25 | 203 | 2024-06-02 |
| 4 | AIRPORT75 | 204 | 2024-06-10 |
Output
| promo_code | campaign |
|---|---|
| CAMPUS30 | Campus |
| EATS100 | Eats launch |
How to solve Uber: Promo Codes Nobody Has Redeemed
The question is about absence: codes for which no redemption row exists. That is an anti join, and SQL offers three common spellings of it.
The solution uses LEFT JOIN: every code is kept, matched to each of its redemptions, and a code with none gets a single row whose redemption columns are NULL. Filtering on r.redemption_id IS NULL keeps exactly those. A code redeemed many times produces many joined rows, but none of them survive the filter, so no duplicates appear. The age condition created_on < '2024-06-01' is then an ordinary filter on the code itself.
NOT EXISTS states the idea most directly and stops at the first redemption found. NOT IN works too here because PromoRedemption.promo_code is never NULL — if it could be, a single NULL would make NOT IN return no rows at all, which is why NOT EXISTS is the safer habit. With an index on PromoRedemption.promo_code all three are a lookup per code.
Reference solution (MySQL)
SELECT p.promo_code, p.campaign
FROM PromoCode p
LEFT JOIN PromoRedemption r ON r.promo_code = p.promo_code
WHERE r.redemption_id IS NULL
AND p.created_on < '2024-06-01'
ORDER BY p.promo_codeAnother way
SELECT promo_code, campaign FROM PromoCode p WHERE created_on < '2024-06-01' AND NOT EXISTS (SELECT 1 FROM PromoRedemption r WHERE r.promo_code = p.promo_code) ORDER BY promo_codeAnother way
SELECT promo_code, campaign FROM PromoCode WHERE created_on < '2024-06-01' AND promo_code NOT IN (SELECT promo_code FROM PromoRedemption) ORDER BY promo_code← Uber: Freight Loads Booked Below the Posted Rate · Uber: Driver Names as Shown in the Rider App →