Archive Table week v week
WITH TB1 AS (
SELECT
DISTINCT ARCHIVE_DATE,
COUNT(*) as no_of_rows,
SUM("DEAL_VALUE_USD_NET_OF_REBATE") as sum_of_usd,
SUM("DEAL_VALUE_LOCAL_NET_OF_REBATE") as sum_of_local
FROM OPEN__B2C__DYNAMICS365__PROD.ARCHIVE."CURATED__D365__ARCHIVE"
WHERE ARCHIVE_DATE > '2023-09-01'
AND "HAS_ORDER" = 'Y'
GROUP BY ALL
)
SELECT *,
LAG(no_of_rows) OVER (ORDER BY ARCHIVE_DATE DESC) as prev_no_of_rows,
LAG(sum_of_usd) OVER (ORDER BY ARCHIVE_DATE DESC) as prev_sum_of_usd,
LAG(sum_of_local) OVER (ORDER BY ARCHIVE_DATE DESC) as prev_sum_of_local
FROM TB1
--Variance:
--9,776 rows last week to this week
--210 rows previous two weeks delta
--133 week before
--
SELECT
'curated__d365__snowflake_data' AS table_name,
COUNT(*) AS no_of_rows_daily
FROM OPEN__B2C__DYNAMICS365__PROD.LATEST.CURATED__D365__SNOWFLAKE_DATA
WHERE "HAS_ORDER" = 'Y'
UNION ALL
All Unique Records with more than 1 row
WITH TB1 AS (SELECT
CASE
WHEN "COUNTRY" = 'SPAIN' OR "COUNTRY" = 'Spain' THEN 'Spain'
WHEN "COUNTRY" = 'Canada' THEN 'Canada'
WHEN "COUNTRY" = 'Italy' OR "COUNTRY" = 'ITALY' THEN 'Italy'
WHEN "COUNTRY" = 'Japan' THEN 'Japan'
WHEN "COUNTRY" = 'Brazil' THEN 'Brazil'
WHEN "COUNTRY" = 'Germany' OR "COUNTRY" = 'Austria' OR "COUNTRY" = 'Switzerland' THEN 'DACH'
WHEN "COUNTRY" = 'United States' OR "COUNTRY" = 'United States of America' THEN 'United States'
WHEN "COUNTRY" = 'United Kingdom' OR "COUNTRY" = 'Ireland' THEN 'UK/Ireland'
WHEN "COUNTRY" = 'Belgium' OR "COUNTRY" = 'BELGIUM' THEN 'Belgium'
WHEN "COUNTRY" = 'France' OR "COUNTRY" = 'FRANCE' THEN 'France'
WHEN "COUNTRY" = 'Portugal' OR "COUNTRY" = 'PORTUGAL' THEN 'Portugal'
WHEN "COUNTRY" = 'Taiwan' OR "COUNTRY" = 'TAIWAN' THEN 'Taiwan'
ELSE 'Global' END AS "territory",
CONCAT("PL","PERIOD") AS pl_period,
"PL",
"PERIOD",
"CAMPAIGN_NAME",
SUM("DEAL_VALUE_USD_NET_OF_REBATE") as revenue_usd_net_of_rebates,
COUNT(*) as number_of_records
FROM
OPEN__B2C__DYNAMICS365__PROD.ARCHIVE."CURATED__D365__ARCHIVE"
WHERE "PERIOD" BETWEEN '2023-09-01' AND '2024-01-01'
AND "ARCHIVE_DATE" = '2023-12-04'
AND "HAS_ORDER" = 'Y'
AND "DATA_SOURCE" IN ('DAZN', 'PG/PD')
GROUP BY ALL)
SELECT * FROM TB1
WHERE number_of_records != 1