Streamhub's marketing team is planning region-specific plan promotions and needs to know which subscription-plan and country combinations exist in the user base.
Write a query to return each unique pairing of plan and country from the users table.
Assumptions:
- The
userstable contains every account on the Streamhub platform. - A pairing is unique only when both
planandcountrydiffer — two users who are both on theproplan in the US collapse to one row.
Output:
- One row per unique combination, with columns
planandcountry.
Schema · analytics 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT DISTINCT
plan,
country
FROM
usersThe shape
DISTINCT plan, country enumerates the segment grid: one row per (plan, country) pair that actually has users in it. The marketing team gets the live cross-tab of plan-by-country directly, without having to imagine which combinations exist.
Clause by clause
SELECT DISTINCT plan, countryreturns the unique two-column combinations from whatever rowsFROMhands up. The deduplication treats(plan, country)as a tuple:pro, USandpro, CAare different rows because the country differs; two users who are bothpro, UScollapse to one row because the tuple matches exactly.FROM usersis the row source — every account on the platform contributes its(plan, country)pair to the candidate set, and the deduplication runs across that set.
The result is a 27-row grid. Four plans (free, starter, pro, enterprise) and a handful of countries don't produce a full 4 × N cartesian product — some combinations have no users in them. enterprise, FR doesn't appear in the result, which means Streamhub has no enterprise customers in France today. For a region-specific plan promotion, that absence is itself a signal: it tells the marketing team where the white space is.
Why this and not enumerate plans and countries separately
Running SELECT DISTINCT plan FROM users gives four rows; SELECT DISTINCT country FROM users gives a country list. Multiplied together, that's the full theoretical grid — 4 × number-of-countries combinations. But that grid includes combinations that don't actually have users, which is misleading for a campaign aimed at existing customers. The single DISTINCT plan, country query returns only the live segments, which is the only set the team can actually target with a plan-specific promotion.
You practiced using DISTINCT to enumerate a cross-tabulation of two categorical columns. The recurring shape is exactly this: "what segments exist in my data" — DISTINCT col1, col2 produces the segment list directly.
