Uber: Promo Codes Nobody Has Redeemed

Easy
976.

Uber: Promo Codes Nobody Has Redeemed

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

ColumnType
promo_codevarchar
campaignvarchar
created_ondate

Primary key: promo_code.

One row per promo code. Codes are upper-case.

Table: PromoRedemption

ColumnType
redemption_idint
promo_codevarchar
rider_idint
redeemed_ondate

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.

Example

The rows must come back in the order shown.

PromoCode

promo_codecampaigncreated_on
EATS100Eats launch2024-05-02
MONSOON25Monsoon rides2024-05-20
RIDE50New rider2024-04-11
AIRPORT75Airport drops2024-05-28
DIWALI40Festive2024-06-03
CAMPUS30Campus2024-05-31

PromoRedemption

redemption_idpromo_coderider_idredeemed_on
1RIDE502012024-04-20
2RIDE502022024-05-01
3MONSOON252032024-06-02
4AIRPORT752042024-06-10

Output

promo_codecampaign
CAMPUS30Campus
EATS100Eats launch
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 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

ColumnType
promo_codevarchar
campaignvarchar
created_ondate

Primary key: promo_code.

One row per promo code. Codes are upper-case.

Table: PromoRedemption

ColumnType
redemption_idint
promo_codevarchar
rider_idint
redeemed_ondate

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_codecampaigncreated_on
EATS100Eats launch2024-05-02
MONSOON25Monsoon rides2024-05-20
RIDE50New rider2024-04-11
AIRPORT75Airport drops2024-05-28
DIWALI40Festive2024-06-03
CAMPUS30Campus2024-05-31

PromoRedemption

redemption_idpromo_coderider_idredeemed_on
1RIDE502012024-04-20
2RIDE502022024-05-01
3MONSOON252032024-06-02
4AIRPORT752042024-06-10

Output

promo_codecampaign
CAMPUS30Campus
EATS100Eats 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_code

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

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