Hugging Face's logo

Spaces:
luli331
/
letudiant_admin
Sleeping

App Files Community
letudiant_admin / backend /tests /test_data_integrity.sql
Luli Maruyama
Deploy app to Hugging Face Space (no binaries)
9e42d3b
Raw History Blame Contribute Delete
5.66 kB
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;