All User and Period Combinations — CROSS JOIN in SQL

The problem

Streamhub's analytics team is building a quarterly reporting template and needs a complete grid of every user across every reporting period — one row per user-period combination, regardless of whether any data appears for that pairing.

Write a query to return the user ID and period name for every possible combination.

Assumptions:

The users table contains every account on the platform.
The periods table contains the calendar windows used to bucket metrics (Q1, Q2, etc.).
The output's row count is the product of the two tables' row counts (every user paired with every period).

Output:

One row per user-period combination, with columns user_id 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,
  p.name AS period_name
FROM
  users u
  CROSS JOIN periods p

The shape

CROSS JOIN with no ON clause pairs every row in users with every row in periods, producing the complete user-by-period grid the template needs. Every user appears four times — once for each quarter — regardless of whether they had any activity in that quarter.

Clause by clause

FROM users u CROSS JOIN periods p is the join. There's no ON clause because CROSS JOIN doesn't take one — the operation is unconditional pairing. If users has 80 rows and periods has 4, the result has 320 rows, the product of the two table sizes.
u.id AS user_id reads the account ID from the users side of each paired row. The table alias u disambiguates the column, since periods also has an id.
p.name AS period_name pulls the quarter label (Q1, Q2, Q3, Q4) from the periods side.

Why this and not `INNER JOIN`

An INNER JOIN requires a match condition. There is no column connecting users to periods — a user doesn't belong to a quarter and a quarter doesn't belong to a user. The relationship the report needs is the absence of a relationship: every user, every quarter, no filtering.

You practiced a CROSS JOIN to build a complete grid of combinations. The recurring shape: when the question is "every X paired with every Y, regardless of whether they're related," CROSS JOIN is the join type with no ON clause — the result is the cartesian product of the two tables.

Return the user ID and period name for every possible combination

The shape

Clause by clause

Why this and not `INNER JOIN`

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 INNER JOIN

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

Why this and not `INNER JOIN`