Microsoft-style Company ChallengeHardVerified answerSQLite live

Support Load Percentage

What percentage of all support tickets does each product account for?

  • Subqueries
  • Aggregation
  • Numeric functions
  • NULL handling
  • Sorting

Challenge brief

Understand the request

Support Analytics is presenting to product teams and needs each product's share of total support load as a percentage to show relative ticket burden.

Calculate ticket percentage per product using a scalar subquery as denominator.

Return

  • product_id
  • ticket_pct (% of total tickets, rounded 2)

Constraints

  • Share = product tickets / all tickets * 100
  • The calculation must remain safe if the dataset has no tickets
  • Show the largest shares first

Data you will use

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

support_tickets

  • ticket_idINTEGER
  • user_idINTEGER
  • product_idINTEGER
  • created_dateDATE
  • statusTEXT

Hints, when you need them

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

Hint 1

You need each product's ticket count divided by the total ticket count. The total is a scalar — compute it as (SELECT COUNT(*) FROM support_tickets). Multiply by 100.0 for floating-point division.

Hint 2

SELECT product_id, ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM support_tickets), 2) AS ticket_pct FROM support_tickets GROUP BY product_id.

Hint 3

Build question 22 from the required result grain: choose the driving table, add only the joins and filters needed for that grain, then apply aggregation and deterministic ordering.

Verified SQL answer

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

Reveal solution and explanation
SELECT product_id, ROUND(100.0 * COUNT(*) / NULLIF((SELECT COUNT(*) FROM support_tickets), 0), 2) AS ticket_pct FROM support_tickets GROUP BY product_id ORDER BY ticket_pct DESC, product_id;

Why this works

The scalar subquery returns 12 (total tickets). 100.0 (not 100) forces floating-point division. Office gets 5/12 = 41.67%, Azure 4/12 = 33.33%, Teams 3/12 = 25%. All three sum to 100%.

Success check

3 products — Office 41.67%, Azure 33.33%, Teams 25%

Expected result

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

product_idticket_pct
141.67
233.33
325

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.