Cross join use case SQL

I recently implemented a cross join for an analytics use case and decided it could be worth documented for those who struggle with understanding when to use cross joins.

A cross join allows you to perform a cartesian product of two tables where each row in table 1 is joined to each row in table 2 as illustrated below;

The syntax does not allow you to specify a join condition as follows:

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

In my use case, I was trying to create a table that had aggregate metrics per user per date. I approached this problem as follows:

1. Create a table with a row for every calendar date since 2021–01–01 using the dbt_utils.date_spine macro e.g https://github.com/tinakiiki/medium_code_references/blob/main/cross_join/dates.sql
2. Cross join the dates table with the user table to generate a row for each user_id, as_of_date
3. Left join to metrics tables and calculate metrics as follows e.g to sum up cumulative orders by user as of each date