Subscribers per Product
How many distinct users are subscribed to each Microsoft product?
- Joins
- Aggregation
- Sorting
- Distinct values
Challenge brief
Understand the request
Product Analytics wants to understand the subscriber base size for each product to inform feature investment decisions.
Count distinct subscribers per product by joining subscriptions to products.
Return
- product_name
- subscribers
Constraints
- Include every catalog product, even when it has no subscribers
- Count each subscribed user once per product
- Show the largest subscriber counts first
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
subscriptions
subscription_idINTEGERuser_idINTEGERproduct_idINTEGERstart_dateDATEend_dateDATEpriceINTEGER
products
product_idINTEGERproduct_nameTEXTcategoryTEXT
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Subscriber counts require joining subscriptions to products for the product name. COUNT(DISTINCT user_id) per product group.
Hint 2
INNER JOIN subscriptions to products on product_id. GROUP BY p.product_name. COUNT(DISTINCT s.user_id) AS subscribers.
Hint 3
Build question 11 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 p.product_name, COUNT(DISTINCT s.user_id) AS subscribers FROM products p LEFT JOIN subscriptions s ON p.product_id = s.product_id GROUP BY p.product_id, p.product_name ORDER BY subscribers DESC, p.product_name;Why this works
Start from products to preserve the empty catalog row and count distinct user identifiers so repeat subscriptions do not inflate subscriber reach.
Success check
3 products — Office leads (5 subscribers), Azure (4), Teams (3). Dynamics has none.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| product_name | subscribers |
|---|---|
| Azure | 5 |
| Microsoft Office | 5 |
| Teams | 3 |
| Dynamics | 0 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Apple
Independent Apple-style product, retail, services, support, workforce, and device-usage SQL practice.
Google
Independent Google-style search, advertising, user-engagement, and video-product SQL practice.
Amazon
Independent Amazon-style e-commerce, warehouse, inventory, and customer analytics SQL practice.
Return to the complete interview preparation experience.