Postgresql – Get missing weeks by country

join;postgresql

I've got one table called "Units_Sold" which has these fields:
year, month, week, country, units

and another table called "Dates" which has these other fields:
year, week, month

The dates table has 1 entry per week/month/year
The Units Sold Table has more than 1 record per week, month, year as it is multiple units sales on a given week

What I'd like is to get the missing units sales weeks BY COUNTRY, this is, all the weeks in which there are no unit sales

I'm not sure how to approach this, if I join the 2 tables I get the weeks in which unit sales is present, that's not an issue, how can I get the inverse by country? that is, all weeks for each country in which there were no sales at all…

Best Answer

Typically using a NOT EXISTS condition is the most efficient way. But you also need to generate the combination of all possible countries and dates through a cross join before finding those that don't exist.

Without a lookup table, this would be something like:

with countries as (
  select distinct country
  from units_sold
)
select c.country, d.year, d.month, d.week
from countries c
  cross join dates d
where not exists (select *
                  from units_sold u
                  where (u.year, u.month, u.week) = (d.year, d.month, d.week))
order by c.country, d.year, d.month, d.week;

If you do have a lookup table with the countries, you can remove the CTE (with countries ...) and replace it with the real table.

Related Question