lesson 23 Joins & sets · intermediate

CROSS JOIN & a complete date grid

Build every combination of dates and entities.

the idea

CROSS JOIN creates every pair of rows. It is useful for a deliberate date × entity grid, but careless use multiplies data. The three dates below are supplied setup data via a VALUES list—you are not being tested on UNION. Focus on CROSS JOIN (and optional signup filtering).

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

viewers4 columns · 8 rows

idinteger · key	countrytext	signup_attext	referred_byinteger
1	US	2025-01-01	NULL
2	US	2025-01-02	1
3	GB	2025-01-02	1
4	IN	2025-01-03	2
5	GB	2025-01-04	NULL

showing 5 of 8 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

SELECT day, v.id AS viewer_id
FROM (
  SELECT column1 AS day
  FROM (
    VALUES ('2025-01-01'), ('2025-01-02'), ('2025-01-03')
  )
) AS days
CROSS JOIN viewers v;

Watch for: The expected grid size is dates × viewers before the signup filter.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Use the provided three dates. These three day values are setup data (a VALUES list). You do not need UNION or recursion—your job is the CROSS JOIN next.
step-1.sql
sql›
```
SELECT column1 AS day
FROM (
  VALUES ('2025-01-01'), ('2025-01-02'), ('2025-01-03')
);
```
2
Pair every date with every viewer. CROSS JOIN multiplies the date list by the viewers. Filter by signup_at only after the grid exists when the task asks for that.
step-2.sql
sql›
```
SELECT day, v.id AS viewer_id
FROM (
  SELECT column1 AS day
  FROM (
    VALUES ('2025-01-01'), ('2025-01-02'), ('2025-01-03')
  )
) AS days
CROSS JOIN viewers v;
```