Spaces:
Paused
Paused
File size: 4,213 Bytes
5a81b95 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | -- view_decision_temporal_trends v1.45
-- Fix: Add committee_referral_decisions pattern to capture =utskottet, utskottet, =utskott values
-- Analysis: 7,049 records were falling into other_decisions instead of proper classification
-- Pattern: UPPER(chamber) ~~ '%UTSKOTT%' catches =utskottet (6501), = utskottet (517), utskottet (12), =utskott (19)
DROP VIEW IF EXISTS view_decision_temporal_trends CASCADE;
CREATE VIEW view_decision_temporal_trends AS
WITH daily_decisions AS (
SELECT
dd.made_public_date AS decision_day,
COUNT(*) AS daily_decisions,
ROUND(100.0 * COUNT(*) FILTER (
WHERE UPPER(dpd.chamber) ~~ '%BIFALL%'
OR UPPER(dpd.chamber) ~~ '%GODKÄNT%'
OR UPPER(dpd.chamber) ~~ '%BIFALLA%'
)::NUMERIC / NULLIF(COUNT(*), 0)::NUMERIC, 2) AS daily_approval_rate,
COUNT(*) FILTER (
WHERE UPPER(dpd.chamber) ~~ '%BIFALL%'
OR UPPER(dpd.chamber) ~~ '%GODKÄNT%'
OR UPPER(dpd.chamber) ~~ '%BIFALLA%'
) AS approved_decisions,
COUNT(*) FILTER (
WHERE UPPER(dpd.chamber) ~~ '%AVSLAG%'
OR UPPER(dpd.chamber) ~~ '%AVSLÅ%'
) AS rejected_decisions,
COUNT(*) FILTER (
WHERE UPPER(dpd.chamber) ~~ '%ÅTERFÖRVISNING%'
OR UPPER(dpd.chamber) ~~ '%ÅTERFÖRVISA%'
) AS referred_back_decisions,
-- NEW: Committee referral decisions (captures =utskottet, = utskottet, utskottet, =utskott)
COUNT(*) FILTER (
WHERE UPPER(dpd.chamber) ~~ '%UTSKOTT%'
AND UPPER(dpd.chamber) !~~ '%ÅTERFÖRVISNING%'
) AS committee_referral_decisions
FROM document_proposal_data dpd
JOIN document_proposal_container dpc ON dpc.proposal_document_proposal_c_0 = dpd.hjid
JOIN document_status_container dsc ON dsc.document_proposal_document_s_0 = dpc.hjid
JOIN document_data dd ON dd.id = dsc.document_document_status_con_0
WHERE dd.made_public_date IS NOT NULL
AND dd.made_public_date >= CURRENT_DATE - INTERVAL '5 years'
AND dpd.chamber IS NOT NULL
AND LENGTH(dpd.chamber) >= 6
AND LENGTH(dpd.chamber) <= 29
GROUP BY dd.made_public_date
)
SELECT
decision_day,
daily_decisions,
daily_approval_rate,
approved_decisions,
rejected_decisions,
referred_back_decisions,
committee_referral_decisions,
ROUND(AVG(daily_decisions) OVER (ORDER BY decision_day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS ma_7day_decisions,
ROUND(AVG(daily_decisions) OVER (ORDER BY decision_day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW), 2) AS ma_30day_decisions,
ROUND(AVG(daily_decisions) OVER (ORDER BY decision_day ROWS BETWEEN 89 PRECEDING AND CURRENT ROW), 2) AS ma_90day_decisions,
ROUND(AVG(daily_approval_rate) OVER (ORDER BY decision_day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW), 2) AS ma_30day_approval_rate,
LAG(daily_decisions, 365) OVER (ORDER BY decision_day) AS decisions_last_year,
(daily_decisions - LAG(daily_decisions, 365) OVER (ORDER BY decision_day)) AS yoy_decisions_change,
ROUND(100.0 * (daily_decisions - LAG(daily_decisions, 365) OVER (ORDER BY decision_day))::NUMERIC
/ NULLIF(LAG(daily_decisions, 365) OVER (ORDER BY decision_day), 0)::NUMERIC, 2) AS yoy_decisions_change_pct,
EXTRACT(YEAR FROM decision_day) AS decision_year,
EXTRACT(MONTH FROM decision_day) AS decision_month,
EXTRACT(WEEK FROM decision_day) AS decision_week,
EXTRACT(DOW FROM decision_day) AS decision_day_of_week,
CASE
WHEN EXTRACT(MONTH FROM decision_day) IN (7, 8) THEN 'Summer Recess'
WHEN EXTRACT(MONTH FROM decision_day) IN (12, 1) THEN 'Winter Recess'
WHEN EXTRACT(MONTH FROM decision_day) IN (2, 3) THEN 'Spring Session'
WHEN EXTRACT(MONTH FROM decision_day) IN (9, 10, 11) THEN 'Autumn Session'
WHEN EXTRACT(MONTH FROM decision_day) IN (4, 5, 6) THEN 'Late Spring Session'
ELSE 'Active Session'
END AS parliamentary_period,
'Q' || EXTRACT(QUARTER FROM decision_day)::TEXT || ' ' || EXTRACT(YEAR FROM decision_day)::TEXT AS decision_quarter
FROM daily_decisions
ORDER BY decision_day DESC;
|