US User and Period Combinations — CROSS JOIN in SQL

The problem

Streamhub's regional analytics team needs a quarterly slot grid for all US-based users to track engagement across periods.

Write a query to return the user ID, user name, and period name for every US-user-quarter combination.

Assumptions:

The users table contains every account on the platform; US users are identified by country = 'US'.
The periods table contains the calendar windows used for reporting.
The country condition narrows the result to US users before they are expanded across periods.

Output:

One row per US-user-quarter combination, with columns user_id, user_name, and period_name.

Schema · analytics 5 tables

users

id integer

name text

email text

country text

plan text

signed_up_at timestamptz

is_active boolean

conversions

id integer

user_id integer

converted_at timestamptz

plan text

amount numeric

sessions

id integer

user_id integer

started_at timestamptz

ended_at? timestamptz

event_count integer

events

id integer

user_id integer

session_id? integer

event_type text

occurred_at timestamptz

properties? jsonb

periods

id integer

name text

start_month integer

end_month integer

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  u.id AS user_id,
  u.name AS user_name,
  p.name AS period_name
FROM
  users u
  CROSS JOIN periods p
WHERE
  u.country = 'US'

The shape

WHERE u.country = 'US' narrows users to the US cohort, and CROSS JOIN periods p expands each surviving user across all four quarters. The output carries three columns — user ID, user name, and quarter label — one row per US-user × quarter pairing.

Clause by clause

FROM users u CROSS JOIN periods p is the unconditional pairing. Every row in users combines with every row in periods.
WHERE u.country = 'US' filters to the US cohort. Because the condition reads a column only from the users side, the effect is to restrict the left side of the cross-product to US accounts. Non-US users drop out, and every US user retains all four period pairings.
u.id AS user_id and u.name AS user_name both read from the users side of each paired row. Two columns from the same source table is fine — there's no rule that says each side of a join can only contribute one column.
p.name AS period_name reads the quarter label from the periods side. The table alias p is what disambiguates this from u.name; both columns are literally called name in their source tables.

Why this and not joining `users` to a country table

The country filter is a simple property of users — a single column. There's no need to bring in another table to scope by it. A WHERE condition on the left side of the cross-product does the work directly and keeps the query to two tables. The cross-product is doing the load-bearing work; the filter is just trimming which rows of the left side participate.

You practiced a CROSS JOIN + WHERE filter that restricts the cross-product by a property of one side. The recurring shape: the user-cohort × period-grid is the foundation for any "engagement by quarter for this segment" report.

Return the user ID, user name, and period name for every US-user-quarter combination

The shape

Clause by clause

Why this and not joining `users` to a country table

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why this and not joining users to a country table

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why this and not joining `users` to a country table