Apple-style Company ChallengeMediumVerified answerSQLite live

App Store Download Revenue by Category

For each app category downloaded by our customers, how many were free vs paid, and what is the total revenue generated?

  • Aggregation
  • CASE expressions
  • Numeric functions
  • Sorting

Challenge brief

Understand the request

App Store Commerce needs to understand which app categories are generating paid download revenue vs free installs to inform developer outreach.

Break down app downloads by category showing free count, paid count, and total revenue from app_downloads.

Return

  • category
  • total_downloads
  • free_downloads
  • paid_downloads
  • total_revenue (rounded 2)

Constraints

  • Count free and paid customer downloads separately
  • Show the highest revenue first, then download volume, then category

Data you will use

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

app_downloads

  • download_idINTEGER
  • categoryVARCHAR(50)
  • priceREAL

Hints, when you need them

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

Hint 1

All data is in app_downloads. GROUP BY category. Use SUM(CASE WHEN price = 0 THEN 1 ELSE 0 END) for free_downloads and SUM(CASE WHEN price > 0 THEN 1 ELSE 0 END) for paid_downloads.

Hint 2

SUM(CASE WHEN price = 0 THEN 1 ELSE 0 END) counts free downloads. SUM(CASE WHEN price > 0 THEN 1 ELSE 0 END) counts paid. SUM(price) gives total revenue. Round to 2 decimals.

Hint 3

Build question 16 from its business grain: identify the driving rows, add only valid relationships, then apply the required filtering, aggregation, and deterministic ordering.

Verified SQL answer

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

Reveal solution and explanation
SELECT category, COUNT(download_id) AS total_downloads, SUM(CASE WHEN price = 0 THEN 1 ELSE 0 END) AS free_downloads, SUM(CASE WHEN price > 0 THEN 1 ELSE 0 END) AS paid_downloads, ROUND(SUM(price), 2) AS total_revenue FROM app_downloads GROUP BY category ORDER BY total_revenue DESC, total_downloads DESC, category

Why this works

SUM(CASE WHEN cond THEN 1 ELSE 0 END) is conditional counting — it sums 1 for rows that match and 0 for others. This is the standard pivot-in-SQL pattern for splitting one column into multiple count columns within a single GROUP BY.

Success check

6 categories — Video leads with $299.99 (Final Cut Pro). Social has 2 downloads but $0 revenue (both free).

Expected result

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

categorytotal_downloadsfree_downloadspaid_downloadstotal_revenue
Video101299.99
Graphics20235.98
Social2200
Entertainment1100
Music1100
Productivity1100

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.