Spaces:
Sleeping
Sleeping
File size: 5,662 Bytes
9e42d3b | 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 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 | PRAGMA foreign_keys = ON;
-- Test report format: each SELECT returns PASS/FAIL with details.
-- 1) Row counts are coherent across core tables
SELECT
'row_counts_present' AS test_name,
CASE
WHEN (SELECT COUNT(*) FROM students) > 0
AND (SELECT COUNT(*) FROM source_students) > 0
AND (SELECT COUNT(*) FROM events) > 0
AND (SELECT COUNT(*) FROM derived_features) = (SELECT COUNT(*) FROM students)
AND (SELECT COUNT(*) FROM lead_scores) = (SELECT COUNT(*) FROM students)
AND (SELECT COUNT(*) FROM monetization) = (SELECT COUNT(*) FROM students)
THEN 'PASS'
ELSE 'FAIL'
END AS status,
(
'students=' || (SELECT COUNT(*) FROM students)
|| ', source_students=' || (SELECT COUNT(*) FROM source_students)
|| ', events=' || (SELECT COUNT(*) FROM events)
|| ', derived=' || (SELECT COUNT(*) FROM derived_features)
|| ', scores=' || (SELECT COUNT(*) FROM lead_scores)
|| ', monetization=' || (SELECT COUNT(*) FROM monetization)
) AS details;
-- 2) 20% fast registration conversion rule on school_group among eligible (visited_site=0)
WITH eligible AS (
SELECT COUNT(*) AS n
FROM students
WHERE student_subgroup = 'school_group' AND visited_site = 0
),
converted AS (
SELECT COUNT(*) AS n
FROM students
WHERE student_subgroup = 'school_group'
AND visited_site = 0
AND registration_channel = 'fast'
),
rate AS (
SELECT
e.n AS eligible_n,
c.n AS converted_n,
CASE WHEN e.n = 0 THEN 0.0 ELSE CAST(c.n AS REAL) / e.n END AS conversion_rate
FROM eligible e
CROSS JOIN converted c
)
SELECT
'fast_registration_conversion_20pct' AS test_name,
CASE WHEN conversion_rate BETWEEN 0.19 AND 0.21 THEN 'PASS' ELSE 'FAIL' END AS status,
'eligible=' || eligible_n || ', converted=' || converted_n || ', rate=' || ROUND(conversion_rate, 4) AS details
FROM rate;
-- 3) Coverage targets within tolerance for registered profiles
WITH reg AS (
SELECT COUNT(*) AS n
FROM students
WHERE is_registered = 1
),
coverage AS (
SELECT
r.n AS registered_n,
AVG(CASE WHEN sp.full_name IS NOT NULL THEN 1.0 ELSE 0.0 END) AS full_name_cov,
AVG(CASE WHEN sp.email IS NOT NULL THEN 1.0 ELSE 0.0 END) AS email_cov,
AVG(CASE WHEN sp.current_study_level IS NOT NULL THEN 1.0 ELSE 0.0 END) AS study_cov,
AVG(CASE WHEN sp.date_of_birth IS NOT NULL THEN 1.0 ELSE 0.0 END) AS dob_cov,
AVG(CASE WHEN sp.phone IS NOT NULL THEN 1.0 ELSE 0.0 END) AS phone_cov,
AVG(CASE WHEN sp.postal_region IS NOT NULL THEN 1.0 ELSE 0.0 END) AS region_cov,
AVG(CASE WHEN sp.gender IS NOT NULL THEN 1.0 ELSE 0.0 END) AS gender_cov,
AVG(CASE WHEN sp.bac_track_type IS NOT NULL THEN 1.0 ELSE 0.0 END) AS bac_cov,
AVG(CASE WHEN sp.fields_interest_count > 0 THEN 1.0 ELSE 0.0 END) AS fields_cov,
AVG(CASE WHEN sp.programme_of_interest IS NOT NULL THEN 1.0 ELSE 0.0 END) AS programme_cov,
AVG(CASE WHEN sc.student_id IS NOT NULL THEN 1.0 ELSE 0.0 END) AS consent_cov,
AVG(CASE WHEN pfs.pre_fair_email_opens > 0 OR pfs.pre_fair_email_clicks > 0 THEN 1.0 ELSE 0.0 END) AS prefair_active_cov
FROM students s
LEFT JOIN student_profiles sp ON sp.student_id = s.id
LEFT JOIN student_consents sc ON sc.student_id = s.id
LEFT JOIN pre_fair_signals pfs ON pfs.student_id = s.id
CROSS JOIN reg r
WHERE s.is_registered = 1
)
SELECT
'coverage_targets_with_tolerance' AS test_name,
CASE
WHEN full_name_cov = 1.0
AND email_cov = 1.0
AND study_cov = 1.0
AND consent_cov = 1.0
AND dob_cov BETWEEN 0.90 AND 0.99
AND phone_cov BETWEEN 0.62 AND 0.78
AND region_cov BETWEEN 0.52 AND 0.68
AND gender_cov BETWEEN 0.84 AND 0.95
AND bac_cov BETWEEN 0.78 AND 0.92
AND fields_cov BETWEEN 0.72 AND 0.88
AND programme_cov BETWEEN 0.52 AND 0.68
AND prefair_active_cov BETWEEN 0.12 AND 0.24
THEN 'PASS'
ELSE 'FAIL'
END AS status,
'registered=' || registered_n
|| ', dob=' || ROUND(dob_cov, 3)
|| ', phone=' || ROUND(phone_cov, 3)
|| ', region=' || ROUND(region_cov, 3)
|| ', gender=' || ROUND(gender_cov, 3)
|| ', bac=' || ROUND(bac_cov, 3)
|| ', fields=' || ROUND(fields_cov, 3)
|| ', programme=' || ROUND(programme_cov, 3)
|| ', prefair_active=' || ROUND(prefair_active_cov, 3)
AS details
FROM coverage;
-- 4) used_ORI and took_test unchanged from source snapshot
SELECT
'used_ori_took_test_unchanged' AS test_name,
CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END AS status,
'mismatches=' || COUNT(*) AS details
FROM students s
JOIN source_students ss
ON ss.fair_id = s.fair_id
AND ss.row_id = s.source_row_id
WHERE s.used_ori <> ss.used_ori
OR s.took_test <> ss.took_test;
-- 5) ORI score must only exist when used_ori = 1
SELECT
'ori_score_condition' AS test_name,
CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END AS status,
'violations=' || COUNT(*) AS details
FROM students s
JOIN pre_fair_signals pfs ON pfs.student_id = s.id
WHERE s.used_ori = 0
AND pfs.ori_test_score IS NOT NULL;
-- 6) No tier_eur > 0 when consent_partner = 0
SELECT
'tier_requires_partner_consent' AS test_name,
CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END AS status,
'violations=' || COUNT(*) AS details
FROM monetization m
JOIN derived_features df
ON df.fair_id = m.fair_id
AND df.student_id = m.student_id
WHERE df.consent_partner = 0
AND m.tier_eur > 0;
-- 7) Monetisable value arithmetic validity
SELECT
'monetisable_value_formula' AS test_name,
CASE WHEN COUNT(*) = 0 THEN 'PASS' ELSE 'FAIL' END AS status,
'violations=' || COUNT(*) AS details
FROM monetization
WHERE ABS(monetisable_value_eur - (tier_eur * resell_factor)) > 0.0001;
|