Uber-style Company ChallengeMediumVerified answerSQLite live

Promotion Utilisation Rate

For each active promotion, what percentage of its maximum allowed uses has already been consumed?

  • Numeric functions
  • NULL handling
  • Filtering
  • Sorting

Challenge brief

Understand the request

Growth Marketing needs to see how close each promotion is to its usage cap to decide which to extend before they expire.

Measure active promotion utilisation while handling promotions whose usage cap is zero.

Return

  • promotion_code
  • description
  • max_uses
  • times_used
  • utilisation_pct (times_used / max_uses * 100, rounded 1)
  • active

Constraints

  • Return only active promotions
  • Return NULL utilisation when maximum uses is zero rather than dividing by zero
  • Order defined utilisation percentages descending, then place undefined values last, with promotion ID as a tie-break

Data you will use

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

promotions

  • promotion_codeVARCHAR(50)
  • descriptionTEXT
  • max_usesINTEGER
  • times_usedINTEGER
  • activeINTEGER

Hints, when you need them

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

Hint 1

All data is in promotions — no JOIN required. The key calculation is utilisation_pct: how much of the max_uses cap has already been consumed. Multiply by 100.0 (not 100) to avoid integer division returning 0.

Hint 2

GROUP BY is not needed here — promotions already has one row per promotion with times_used and max_uses. Simply SELECT the columns and compute: ROUND(times_used * 100.0 / max_uses, 1) AS utilisation_pct. ORDER BY utilisation_pct DESC.

Hint 3

Filter the promotion population first, protect the usage cap before division, and explicitly position undefined rates after defined rates.

Verified SQL answer

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

Reveal solution and explanation
SELECT promotion_code, description, max_uses, times_used, ROUND(times_used * 100.0 / NULLIF(max_uses, 0), 1) AS utilisation_pct, active FROM promotions WHERE active = 1 ORDER BY utilisation_pct IS NULL, utilisation_pct DESC, promotion_id

Why this works

The active filter matches the business request. A zero maximum-use cap is converted to a NULL denominator, preventing a division error and representing an undefined utilisation rate.

Success check

3 active promotions; PAUSED0 is retained with NULL utilisation and sorted last

Expected result

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

promotion_codedescriptionmax_usestimes_usedutilisation_pctactive
SAVE5$5 off any ride50031262.41
FIRST1010% off first ride100024524.51
PAUSED0Active test promotion with no configured capacity00NULL1

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.