/*
DBS Bank SQL Interview Question

You are given a table named sls_tbl that contains sales data for a specific product.

The table records the sales amount (sls_amt) for a product identified by pid on specific dates (sls_dt).
The sales data is recorded on a weekly basis, meaning that sales are expected to be recorded every 7 days.

Your task is to identify the missing weeks in the sales data.
Specifically, you need to find the dates on which sales data is expected but not recorded in the table.
*/

-- Schema Setup
CREATE TABLE sls_tbl (
    pid INT,
    sls_dt DATE,
    sls_amt INT
);

INSERT INTO sls_tbl (pid, sls_dt, sls_amt) VALUES
(201, '2024-07-11', 140),
(201, '2024-07-18', 160),
(201, '2024-07-25', 150),
(201, '2024-08-01', 180),
(201, '2024-08-15', 170),
(201, '2024-08-29', 130);

-- Solution
WITH RECURSIVE week_dates AS
(
 SELECT
    MIN(sls_dt) AS dt
 FROM sls_tbl
 UNION ALL
 SELECT
    dt + INTERVAL 7 DAY
 FROM week_dates
 WHERE dt < (SELECT MAX(sls_dt) FROM sls_tbl)
)
    
SELECT
    dt AS sls_dt
FROM week_dates
WHERE dt NOT IN (SELECT sls_dt FROM sls_tbl);