It's common for there to be gaps or missing values in an SQL table. For example you may have daily traffic by source, but on some low volume days around Christmas there are no values in the low traffic sources. Missing values can really complicate some calculations like moving averages, and some times you need a way of filling them in. This is straightforward with a cross join.
You need all the possible variables you're filling in, and the value to fill.
For example you might have the
daily table containing each device, traffic source and date the number of visitors.
There are some missing combinations of valid (device, source) and date with visitors, and we want to fill them with 0s. We could get all the possible combinations of device and source with a simple query:
select device, source from daily group by 1, 2
Similarly we could generate all possible dates; if some may be missing for every device and source it's best to use something like generate series:
SELECT date FROM generate_series( (select min(date) from daily), (select max(date) from daily), '1 day') as dates(date)
Then we can generate all possible combinations with a cross join. The exact syntax may vary by database; I'll assume here that separating with commas does the trick (at least Presto and Postgres).
select device, source, date from ( select device, source from daily group by 1, 2 ), generate_series( (select min(date) from daily), (select max(date) from daily), '1 day') as dates(date)
Finally we can fill in the data from the underlying table with a coalesce, using the default value of 0 for missing entries:
select device, source, date, coalesce(daily.users, 0) as users from ( select device, source, date from ( select device, source from daily group by 1, 2 ), generate_series( (select min(date) from daily), (select max(date) from daily), '1 day') as dates(date) ) xjoin left join daily on xjoin.device = daily.device and xjoin.source = daily.source and xjoin.date = daily.date
Finally it's always good to verify the table is actually as expected.
Suppose we materialised the above query into
select device, source, min(date) as first_date, max(date) as last_date, count(distinct date) as dates, count(*) as rows, count(users) as user_rows
We would expect the last 3 columns to be the same and equal to the number of days between first date and last date (which you could add with the database specific function to calculate the difference between dates).
This is a useful pattern for filling in missing values in tables, and can be used in more complex scenarios where the filled value depends on the parameters.