Uber-style Company ChallengeMediumVerified answerSQLite live

Promotion Effectiveness Analysis

For each promotion that has been used at least once, what was the total discount given and average discount per use?

  • Joins
  • Aggregation
  • Numeric functions
  • Sorting

Challenge brief

Understand the request

Growth Marketing is reviewing which promotions are actually being redeemed to decide which to extend and which to retire.

Analyse promotion redemption showing code, description, times used, total discount, and average discount per use.

Return

  • promotion_code
  • description
  • times_used
  • total_discount (rounded 2)
  • avg_discount_per_use (rounded 2)

Constraints

  • Return promotions with at least one recorded redemption
  • Order by total discount descending, then promotion ID

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

promotions

  • promotion_idINTEGER
  • promotion_codeVARCHAR(50)
  • descriptionTEXT

rider_promotions

  • rider_promotion_idINTEGER
  • promotion_idINTEGER
  • discount_appliedREAL

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Promotion details are in promotions. Redemption records and discount amounts are in rider_promotions. INNER JOIN on promotion_id so only used promotions appear.

Hint 2

INNER JOIN promotions to rider_promotions on promotion_id. GROUP BY promotion. COUNT(rider_promotion_id) AS times_used. SUM(discount_applied) AS total_discount. AVG(discount_applied) AS avg_discount.

Hint 3

Use recorded redemptions as the qualifying fact set, aggregate discounts by promotion, and use the promotion key to stabilize ties.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT p.promotion_code, p.description, COUNT(rp.rider_promotion_id) AS times_used, ROUND(SUM(rp.discount_applied), 2) AS total_discount, ROUND(AVG(rp.discount_applied), 2) AS avg_discount_per_use FROM promotions p INNER JOIN rider_promotions rp ON p.promotion_id = rp.promotion_id GROUP BY p.promotion_id, p.promotion_code, p.description ORDER BY total_discount DESC, p.promotion_id

Why this works

All 3 promotions have exactly 1 redemption each. INNER JOIN means unused promotions would not appear (there are none here). The discount amounts differ because each promotion type gives a different benefit: percentage off, flat amount, or weekend discount.

Success check

3 promotions — WEEKEND20 ($7 total), SAVE5 ($5), FIRST10 ($1.85)

Expected result

Use this output to verify values, aliases, ordering, and row count.

promotion_codedescriptiontimes_usedtotal_discountavg_discount_per_use
WEEKEND2020% off weekend rides177
SAVE5$5 off any ride155
FIRST1010% off first ride11.851.85

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.