Skip to content

Instantly share code, notes, and snippets.

@jackross
Last active May 25, 2016 05:00
Show Gist options
  • Save jackross/d9ee29b07a2eb73d184baaf7607c7bb0 to your computer and use it in GitHub Desktop.
Save jackross/d9ee29b07a2eb73d184baaf7607c7bb0 to your computer and use it in GitHub Desktop.
Cumulative Grower appearance in file
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
;
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