dremio-examples/sql_examples/higher_ed_enrollment_funnel.sql at main · developer-advocacy-dremio/dremio-examples · GitHub

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
/*
 * Higher Education Enrollment Funnel Demo
 *
 * Scenario:
 * Analyzing the student admissions pipeline from Application to Matriculation.
 *
 * Architecture: Medallion (Bronze -> Silver -> Gold)
 */

-------------------------------------------------------------------------------
-- 0. SETUP
-------------------------------------------------------------------------------
CREATE FOLDER IF NOT EXISTS EducationDB;
CREATE FOLDER IF NOT EXISTS EducationDB.HigherEd;
CREATE FOLDER IF NOT EXISTS EducationDB.HigherEd.Bronze;
CREATE FOLDER IF NOT EXISTS EducationDB.HigherEd.Silver;
CREATE FOLDER IF NOT EXISTS EducationDB.HigherEd.Gold;

-------------------------------------------------------------------------------
-- 1. BRONZE LAYER
-------------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS EducationDB.HigherEd.Bronze.Applicants (
    ApplicantID INT,
    Term VARCHAR, -- Fall 2025
    MajorOfInterest VARCHAR,
    GPA DOUBLE,
    Status VARCHAR, -- Applied, Admitted, Waitlisted, Enrolled, Denied
    DepositPaid BOOLEAN
);

INSERT INTO EducationDB.HigherEd.Bronze.Applicants VALUES
(1, 'Fall 2025', 'Computer Science', 3.8, 'Enrolled', true),
(2, 'Fall 2025', 'Engineering', 3.5, 'Admitted', false), -- Melt
(3, 'Fall 2025', 'Business', 3.2, 'Denied', false),
(4, 'Fall 2025', 'Computer Science', 3.9, 'Enrolled', true),
(5, 'Fall 2025', 'Biology', 3.6, 'Waitlisted', false),
(6, 'Fall 2025', 'Business', 3.4, 'Admitted', true), -- Enrolled status pending?
(7, 'Fall 2025', 'Engineering', 3.7, 'Enrolled', true),
(8, 'Fall 2025', 'Psychology', 3.1, 'Denied', false),
(9, 'Fall 2025', 'Computer Science', 4.0, 'Admitted', false),
(10, 'Fall 2025', 'Business', 3.5, 'Enrolled', true),
(11, 'Fall 2025', 'Biology', 3.8, 'Waitlisted', false);

-------------------------------------------------------------------------------
-- 2. SILVER LAYER
-------------------------------------------------------------------------------

CREATE OR REPLACE VIEW EducationDB.HigherEd.Silver.FunnelMetrics AS
SELECT
    Term,
    MajorOfInterest,
    Status,
    DepositPaid,
    CASE
        WHEN Status = 'Enrolled' OR (Status = 'Admitted' AND DepositPaid = true) THEN 'Matriculated'
        ELSE Status
    END AS FinalStage
FROM EducationDB.HigherEd.Bronze.Applicants;

-------------------------------------------------------------------------------
-- 3. GOLD LAYER
-------------------------------------------------------------------------------

CREATE OR REPLACE VIEW EducationDB.HigherEd.Gold.MajorYield AS
SELECT
    MajorOfInterest,
    COUNT(*) AS TotalApplicants,
    SUM(CASE WHEN Status IN ('Admitted', 'Enrolled') THEN 1 ELSE 0 END) AS AdmittedCount,
    SUM(CASE WHEN FinalStage = 'Matriculated' THEN 1 ELSE 0 END) AS EnrolledCount,
    (CAST(SUM(CASE WHEN FinalStage = 'Matriculated' THEN 1 ELSE 0 END) AS DOUBLE) /
     NULLIF(SUM(CASE WHEN Status IN ('Admitted', 'Enrolled') THEN 1 ELSE 0 END), 0)) * 100 AS YieldRate
FROM EducationDB.HigherEd.Silver.FunnelMetrics
GROUP BY MajorOfInterest;

-------------------------------------------------------------------------------
-- 4. AGENT PROMPTS
-------------------------------------------------------------------------------
/*
PROMPT:
"Calculate the YieldRate for 'Computer Science' majors in EducationDB.HigherEd.Gold.MajorYield."
*/