Apple-style Company ChallengeMediumVerified answerSQLite live

App Category Performance Analysis

For each App Store category with at least 5 million total downloads, show total downloads, average rating, app count, and rank by download volume.

  • Window functions
  • Aggregation
  • HAVING
  • Numeric functions
  • Sorting

Challenge brief

Understand the request

App Store Analytics is preparing the annual App Store Trends report and needs download volume and quality metrics by category.

Analyse App Store performance by category — total downloads, average rating, app count, and RANK() by download volume.

Return

  • category
  • total_downloads
  • avg_rating (rounded 2)
  • app_count
  • category_rank (RANK by total_downloads desc)

Constraints

  • Return categories with at least 5 million downloads
  • Categories with equal downloads share a rank
  • Resolve display ties by category

Data you will use

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

app_store_apps

  • app_idINTEGER
  • categoryVARCHAR(50)
  • downloadsINTEGER
  • ratingREAL

Hints, when you need them

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

Hint 1

All data is in app_store_apps. Single-table aggregation: GROUP BY category, SUM(downloads), AVG(rating), COUNT(app_id). Add HAVING SUM >= 5000000. RANK() OVER for the rank.

Hint 2

GROUP BY category. SUM(downloads) AS total_downloads. ROUND(AVG(rating), 2). COUNT(app_id). HAVING SUM(downloads) >= 5000000. RANK() OVER (ORDER BY SUM(downloads) DESC).

Hint 3

Build question 9 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, SUM(downloads) AS total_downloads, ROUND(AVG(rating), 2) AS avg_rating, COUNT(app_id) AS app_count, RANK() OVER (ORDER BY SUM(downloads) DESC) AS category_rank FROM app_store_apps GROUP BY category HAVING SUM(downloads) >= 5000000 ORDER BY total_downloads DESC, category;

Why this works

Single-table GROUP BY. RANK() runs after aggregation so it ranks grouped sums. All 10 app categories in the dataset have >= 5M downloads so all qualify. Entertainment has 3 apps (TikTok, Netflix, YouTube) summing to 125M.

Success check

10 categories qualify — Entertainment leads (125M downloads), Health is last at 9M. Games and Health categories fall below 5M threshold would be excluded, but here both qualify.

Expected result

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

categorytotal_downloadsavg_ratingapp_countcategory_rank
Entertainment1250000004.531
Social950000004.7522
Music350000004.913
Games340000004.0524
Education250000004.715
Travel200000004.216
Productivity180000004.617
Business150000004.318
Graphics140000004.6529
Health90000004.8110

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.