-- Created on savesnippets.com · https://savesnippets.com/GOzi5g6uzLdzx1
-- FULL OUTER — total reconciliation between two source systems
SELECT
    COALESCE(a.id, b.id) AS id,
    a.name AS name_in_a,
    b.name AS name_in_b
FROM   import_source_a a
FULL   OUTER JOIN import_source_b b ON a.id = b.id
WHERE  a.name IS DISTINCT FROM b.name;   -- show only mismatches

-- CROSS JOIN — every region × every product
SELECT r.region, p.product, COALESCE(SUM(s.amount), 0) AS revenue
FROM   regions r
CROSS  JOIN products p
LEFT   JOIN sales s
       ON s.region = r.region AND s.product = p.product
GROUP  BY r.region, p.product
ORDER  BY r.region, p.product;
-- Without CROSS JOIN, region/product combos with zero sales would be missing.

-- Generate a date spine (PostgreSQL)
SELECT d::date AS day
FROM   generate_series('2025-01-01'::date, '2025-01-31'::date, INTERVAL '1 day') AS d;