Cumulative Grower appearance in file

changes_by_version.sql

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
;

cum_growers.sql

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