SELECT
  COALESCE(a.product_id, b.product_id) AS product_id,  -- recover the key even when one side is NULL
  COALESCE(a.name, b.name)             AS name,
  a.price AS price_kikan,
  b.price AS price_ec,
  CASE
    WHEN a.product_id IS NULL THEN 'Missing from core'
    WHEN b.product_id IS NULL THEN 'Missing from EC'
    WHEN a.price <> b.price  THEN 'Price mismatch'
  END AS status
FROM products_kikan AS a
FULL OUTER JOIN products_ec AS b
  ON a.product_id = b.product_id
WHERE a.product_id IS NULL      -- ③ Missing from core
   OR b.product_id IS NULL      -- ② Missing from EC
   OR a.price <> b.price        -- ① Price mismatch (NULL rows are UNKNOWN, but the two IS NULL conditions rescue them)
ORDER BY product_id;

-- FULL OUTER JOIN databases without FULL OUTER JOIN (such as MySQL before 8.0):portable version:
--   LEFT JOIN result
--   UNION
--   RIGHT JOIN result (or LEFT JOIN with the sides swapped)
-- to reproduce the same three zones. 

/*
  Execution order:
  1. FROM products_kikan AS a          → read the core master
  2. FULL OUTER JOIN products_ec AS b  → reconcile both tables
  3. WHERE difference conditions                        → exclude complete matches
  4. SELECT                            → recover with COALESCE and classify with CASE
  5. ORDER BY product_id               → sort and output
  */

WITH child_sales AS (
  SELECT dept_id, SUM(amount) AS sales_total
  FROM sales
  GROUP BY dept_id              -- 1:N → 1:1 (aggregate at child-department grain)
)
SELECT
  p.dept_name AS parent_dept,
  SUM(cs.sales_total) AS total_sales,
  SUM(t.target)       AS total_target,
  ROUND(SUM(cs.sales_total)::NUMERIC / SUM(t.target) * 100, 1) AS achievement_rate
FROM departments AS c                                  -- Child department
INNER JOIN child_sales  AS cs ON c.dept_id   = cs.dept_id  -- 1:1
INNER JOIN dept_targets AS t  ON c.dept_id   = t.dept_id   -- 1:1
INNER JOIN departments  AS p  ON c.parent_id = p.dept_id   -- Self-join (child N: parent 1)
GROUP BY p.dept_id, p.dept_name   -- roll up at parent-department grain (Roll-up)
ORDER BY p.dept_id;

/*
  Execution order:
  1. CTE child_sales                  → pre-aggregate sales
  2. FROM departments AS c            → read the child-department side
  3. INNER JOIN child_sales AS cs     → join on dept_id
  4. INNER JOIN dept_targets AS t     → join on dept_id
  5. INNER JOIN departments AS p      → self-join to attach the parent name
  6. GROUP BY p.dept_id, p.dept_name  → aggregate per parent department
  7. SUM, ROUND                       → calculate sales, target, and achievement rate
  8. SELECT, ORDER BY                 → project and sort columns
  */

SELECT
  s.user_id,
  s.plan,
  COUNT(DISTINCT l.used_on) AS active_days  -- “day count”grain: same-day exclude duplicates and skip NULL
FROM subscriptions AS s
LEFT JOIN usage_logs AS l
  ON  s.user_id = l.user_id
  AND l.used_on BETWEEN s.start_date AND s.end_date  -- Non-equi join (put it in the ON clause)
GROUP BY s.user_id, s.plan
ORDER BY s.user_id;

/*
  Execution order:
  1. FROM subscriptions AS s     → read the contract master
  2. LEFT JOIN usage_logs AS l   → join in-period logs (non-equi)
  3. GROUP BY s.user_id, s.plan  → aggregate per contract
  4. COUNT(DISTINCT l.used_on)   → remove duplicate days and aggregate day count
  5. SELECT, ORDER BY            → project and sort columns
  */

SELECT
  u.user_id,
  u.user_name
FROM users AS u
JOIN purchases AS p
  ON u.user_id = p.user_id
GROUP BY u.user_id, u.user_name
HAVING COUNT(*) FILTER (WHERE p.product_name IN ('Coffee', 'Tea')) >= 1  -- Include: A or B at least once
   AND COUNT(*) FILTER (WHERE p.product_name = 'Cake') = 0          -- Exclude: zero occurrences of C
ORDER BY u.user_id;

-- For databases that do not support FILTER (MySQL and similar databases) to  of portable version:
--   HAVING SUM(CASE WHEN p.product_name IN ('Coffee','Tea') THEN 1 ELSE 0 END) >= 1
--      AND SUM(CASE WHEN p.product_name = 'Cake' THEN 1 ELSE 0 END) = 0

/*
  Execution order:
  1. FROM users AS u                  → read users
  2. JOIN purchases AS p              → join purchase history (1:N)
  3. GROUP BY u.user_id, u.user_name  → aggregate per user
  4. HAVING conditional aggregation                   → use FILTER to keep groups meeting the conditions
  5. SELECT, ORDER BY                 → project and sort
  */