Last active
May 25, 2016 05:00
-
-
Save jackross/d9ee29b07a2eb73d184baaf7607c7bb0 to your computer and use it in GitHub Desktop.
Cumulative Grower appearance in file
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH _data AS | |
( | |
SELECT | |
* | |
,EXTRACT(YEAR FROM invoice_date + INTERVAL '3 MONTHS') AS season | |
FROM public.dcp_sales_log | |
) | |
,_new AS | |
( | |
SELECT | |
created_by AS version | |
,season | |
,'new'::text AS status | |
,COUNT(*) AS count | |
FROM _data | |
WHERE seq = 1 | |
GROUP BY | |
created_by | |
,season | |
) | |
,_updates AS | |
( | |
SELECT | |
updated_by AS version | |
,season | |
,'update'::text AS status | |
,COUNT(*) AS count | |
FROM _data | |
WHERE status IN ('updated') | |
GROUP BY | |
updated_by | |
,season | |
) | |
,_deletes AS | |
( | |
SELECT | |
updated_by AS version | |
,season | |
,'delete'::text AS status | |
,COUNT(*) AS count | |
FROM _data | |
WHERE status IN ('deleted') | |
GROUP BY | |
updated_by | |
,season | |
) | |
,_undeletes AS | |
( | |
SELECT | |
created_by AS version | |
,season | |
,'undelete'::text AS status | |
,COUNT(*) AS count | |
FROM _data l | |
WHERE seq > 1 | |
AND EXISTS (SELECT * FROM _data d WHERE d.status = 'deleted' AND d.transaction_id = l.transaction_id AND d.seq = l.seq - 1) | |
GROUP BY | |
created_by | |
,season | |
) | |
,_union AS | |
( | |
SELECT * FROM _new | |
UNION ALL | |
SELECT * FROM _updates | |
UNION ALL | |
SELECT * FROM _deletes | |
UNION ALL | |
SELECT * FROM _undeletes | |
) | |
SELECT | |
version | |
,season | |
,MAX(CASE WHEN status = 'new' THEN count ELSE 0 END) AS new_count | |
,MAX(CASE WHEN status = 'update' THEN count ELSE 0 END) AS udpate_count | |
,MAX(CASE WHEN status = 'delete' THEN count ELSE 0 END) AS delete_count | |
,MAX(CASE WHEN status = 'undelete' THEN count ELSE 0 END) AS undelete_count | |
FROM _union | |
GROUP BY | |
version | |
,season | |
ORDER BY | |
version DESC | |
,season DESC | |
; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH _data AS | |
( | |
SELECT | |
* | |
,EXTRACT(YEAR FROM invoice_date + INTERVAL '3 MONTHS') AS season | |
,TO_DATE(LEFT(created_by, 8), 'YYYYMMDD') AS provided_on | |
FROM public.dcp_sales_log | |
WHERE manufacturer_code = 'Dupont' | |
) | |
,_growers AS | |
( | |
SELECT | |
grower_id | |
,MIN(provided_on) AS provided_on | |
,MIN(reported_date) AS reported_on | |
,MIN(invoice_date) AS invoiced_on | |
FROM _data | |
WHERE season = 2015 | |
GROUP BY | |
grower_id | |
) | |
,_report AS | |
( | |
SELECT | |
'provided' AS report_type | |
,provided_on AS report_date | |
,COUNT(*) AS growers_count | |
,SUM(COUNT(*)) OVER (ORDER BY provided_on) AS cum_growers_count | |
FROM _growers | |
GROUP BY | |
provided_on | |
UNION ALL | |
SELECT | |
'reported' AS report_type | |
,reported_on AS report_date | |
,COUNT(*) AS growers_count | |
,SUM(COUNT(*)) OVER (ORDER BY reported_on) AS cum_growers_count | |
FROM _growers | |
GROUP BY | |
reported_on | |
UNION ALL | |
SELECT | |
'invoiced' AS report_type | |
,invoiced_on AS report_date | |
,COUNT(*) AS growers_count | |
,SUM(COUNT(*)) OVER (ORDER BY invoiced_on) AS cum_growers_count | |
FROM _growers | |
GROUP BY | |
invoiced_on | |
) | |
SELECT | |
report_date | |
,report_type | |
,MAX(MAX(cum_growers_count)) OVER (PARTITION BY report_type ORDER BY report_date) AS cum_growers_count | |
FROM _report | |
GROUP BY | |
report_date | |
,report_type | |
ORDER BY | |
report_date ASC | |
,report_type ASC |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment