Skip to content

Instantly share code, notes, and snippets.

@jackross
Created February 25, 2015 02:51
Show Gist options
  • Save jackross/38a18921f5f57ac516ac to your computer and use it in GitHub Desktop.
Save jackross/38a18921f5f57ac516ac to your computer and use it in GitHub Desktop.
Compare Actual Mail Plan to Forecasted Mail Plan
DECLARE @forecast_snapshot_id uniqueidentifier = '54266D12-8B13-4C23-9BDF-A740580ECFB2';
WITH _sources AS
(
SELECT
CASE
WHEN source LIKE 'MB[QL]%'
THEN LEFT(source, 7)
ELSE LEFT(source, 9)
END AS label
,technique
,COALESCE(mailed_on, '1999-12-31') AS dropped_on
,solicitations_count_source
FROM usga_stage.dimensions.sources
WHERE initiative_month >= '2014-08-01'
AND source LIKE 'MB[^NU]%'
AND solicitations_count_source > 0
)
,_actuals AS
(
SELECT
label
,technique
,dropped_on
,SUM(solicitations_count_source) AS sols_count
FROM _sources
GROUP BY
label
,technique
,dropped_on
)
,_forecast AS
(
SELECT
label
,technique
,mail_plan_event_on AS dropped_on
,SUM(simulated_targets_count) AS sols_count
FROM usga_apps.seer.simulator_log
WHERE forecast_snapshot_id = @forecast_snapshot_id
AND technique IN ('MM', 'MW')
AND simulated_targets_count > 0
GROUP BY
label
,technique
,mail_plan_event_on
)
,_union AS
(
SELECT
label
,technique
,dropped_on
FROM _actuals
UNION
SELECT
label
,technique
,dropped_on
FROM _forecast
)
SELECT
u.label
,u.technique
,u.dropped_on
,a.sols_count AS actuals_sols_count
,f.sols_count AS forecast_sols_count
FROM _union u
LEFT OUTER JOIN _actuals a ON a.label = u.label AND a.technique = u.technique AND a.dropped_on = u.dropped_on
LEFT OUTER JOIN _forecast f ON f.label = u.label AND f.technique = u.technique AND f.dropped_on = u.dropped_on
ORDER BY
label
,technique
,dropped_on
;
@KevinFalank
Copy link

Updated with more data columns:

DECLARE @forecast_snapshot_id uniqueidentifier = '54266D12-8B13-4C23-9BDF-A740580ECFB2';

WITH _sources AS
(
  SELECT
     CASE
       WHEN source LIKE 'MB[QL]%'
       THEN LEFT(source, 7)
       ELSE LEFT(source, 9)
     END                               AS label
    ,technique
    ,COALESCE(mailed_on, '1999-12-31') AS dropped_on
    ,solicitations_count_source
        ,adjusted_payments_count
        ,payments_count_source
  FROM usga_stage.dimensions.sources
  WHERE initiative_month >= '2014-08-01'
    AND source LIKE 'MB[^NU]%'
    AND solicitations_count_source > 0
)
,_actuals AS
(
  SELECT
     label
    ,technique
    ,dropped_on
        ,SUM(adjusted_payments_count) AS adjusted_payments_count
        ,SUM(payments_count_source)   AS payments_count_source
    ,SUM(solicitations_count_source)                                                            AS sols_count
        ,SUM(adjusted_payments_count) / CAST(SUM(solicitations_count_source) AS FLOAT) AS adjusted_response_rate
        ,SUM(payments_count_source) / CAST(SUM(solicitations_count_source) AS FLOAT)   AS response_rate_source
  FROM _sources
  GROUP BY
     label
    ,technique
    ,dropped_on
)
,_forecast AS
(
  SELECT
     label
    ,technique
    ,mail_plan_event_on           AS dropped_on
    ,SUM(simulated_targets_count) AS sols_count
  FROM usga_apps.seer.simulator_log
  WHERE forecast_snapshot_id = @forecast_snapshot_id
    AND technique IN ('MM', 'MW')
    AND simulated_targets_count > 0
  GROUP BY
     label
    ,technique
    ,mail_plan_event_on
)
,_union AS
(
  SELECT
     label
    ,technique
    ,dropped_on
  FROM _actuals
  UNION
  SELECT
     label
    ,technique
    ,dropped_on
  FROM _forecast
)
SELECT
   u.label
  ,u.technique
  ,u.dropped_on
  ,a.sols_count      AS actuals_sols_count
  ,f.sols_count      AS forecast_sols_count
    ,a.payments_count_source
    ,a.adjusted_payments_count
    ,a.response_rate_source
    ,a.adjusted_response_rate 
FROM _union u
LEFT OUTER JOIN _actuals  a ON a.label = u.label AND a.technique = u.technique AND a.dropped_on = u.dropped_on
LEFT OUTER JOIN _forecast f ON f.label = u.label AND f.technique = u.technique AND f.dropped_on = u.dropped_on
--WHERE a.sols_count is null
ORDER BY
   label
  ,technique
  ,dropped_on
;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment