# Signal and Silence > They opened the assignment. Did they actually read it? Canonical URL: Domain: Data Modeling · Difficulty: medium · Seniority: L4 ## Problem We run a digital classroom platform for K-12 schools where teachers post assignments and students submit them, sometimes past the due date. Leadership wants engagement insights that separate students who merely saw an assignment surface in their feed from those who actually opened and read it, and that stay accurate even as rosters change: students transfer or drop mid-semester, and the same course runs as different sections under different teachers each school year. Design a model that keeps these engagement rates correct for both a single teacher's roster and a district-wide dashboard. ## Worked solution and explanation ### Strip the costume This is two grain traps stacked under an innocent 'engagement insights' ask, and the second one is the killer. Trap one: 'Algebra I' is not one thing. It is a curriculum template AND the specific section Mr. Li runs this fall, and if you fold them into a single classes table, one curriculum tweak rewrites every section row and teacher attribution turns to mush. Trap two, the one that actually separates people: the read-rate denominator is a point-in-time roster, not today's roster. Anyone can write COUNT(reads) over COUNT(students). The tell a senior watches for is whether you scope the roster to the SAME window as the events. Get it wrong and every kid who dropped mid-week vanishes from the denominator while their reads stay in the numerator, so read_rate quietly climbs past what is real and leadership celebrates a number that is pure churn artifact. > **Trick to solving** > > There are two entities pretending to be one (course vs class instance), two events pretending to be one (impression vs read), and two facts at different grains (engagement vs submission). Name all three splits before you draw a single box, then make the enrollment bridge carry dates so the denominator is always a function of time, never a snapshot of 'now'. ### How to build it #### Step 1: Split the curriculum from the occurrence Keep dim_courses as the reusable template and dim_class_instances as one teacher teaching one section in one school year. They feed different questions: curriculum effectiveness rolls up on course_key, teacher performance rolls up on the instance. Collapse them and you cannot answer either cleanly, and a syllabus edit stampedes across every historical section. #### Step 2: Make enrollment a dated bridge, not a column bridge_class_enrollment carries (student_key, class_instance_key, enrolled_at, dropped_at). Rosters churn constantly, so membership is a fact with a lifespan. This is the piece that lets you reconstruct who was enrolled during any window instead of assuming everyone currently listed was always there. #### Step 3: Model impression and read as distinct event types An impression is 'surfaced in the feed'; a read is 'opened and dwelt on'. Same grain, same table, different event_type value, never merged into one 'view' count. The whole business question ('who actually read it') dies the moment those two share a column. #### Step 4: Keep submissions on their own fact fact_assignment_submissions lives at one row per student per assignment with its own measures (submitted_at, grade, is_late). That grain is coarser than engagement events. Jam them together and you lose the ability to compute completion and lateness without fighting a mixed grain on every aggregate. ### The shape Course and class instance as separate dimensions, enrollment as a dated bridge, and two fact tables at two grains. The bridge is what keeps every engagement rate honest as students come and go. > **Why the bridge earns its keep** > > Because membership carries enrolled_at and dropped_at, the denominator becomes a query over a time window instead of a snapshot. That single design choice is what lets the same model answer 'this teacher's roster this week' and 'district-wide last quarter' without either drifting. The price is one extra join on every classroom report, which is cheap next to a metric you can trust. > **Interviewers watch for** > > The candidate who says, in the first minute, 'wait, is a course the curriculum or the section, and does a view mean it appeared or that they read it' has already shown the seniority tell. The weak signal is someone who counts a single 'view' column over the current roster and cannot explain what happens to the rate when a student drops. > **Common pitfall** > > Dividing a past-window engagement count by today's roster. Every student who dropped inside that window disappears from the denominator while their reads linger in the numerator, so the rate inflates and the dashboard trends up for a reason that has nothing to do with real engagement. Scope the roster to the same window as the events, every time. ### The query that proves it **Read rate by class instance, last week** ```sql SELECT ci.class_instance_key, COUNT(DISTINCT CASE WHEN e.event_type = 'read' THEN e.student_key END) * 1.0 / NULLIF(COUNT(DISTINCT b.student_key), 0) AS read_rate FROM dim_class_instances ci JOIN bridge_class_enrollment b ON b.class_instance_key = ci.class_instance_key AND b.enrolled_at <= CURRENT_DATE AND (b.dropped_at IS NULL OR b.dropped_at > CURRENT_DATE - INTERVAL '7 days') LEFT JOIN fact_engagement_events e ON e.class_instance_key = ci.class_instance_key AND e.student_key = b.student_key AND e.event_ts >= CURRENT_DATE - INTERVAL '7 days' GROUP BY ci.class_instance_key ``` *Both the numerator's read window and the bridge's active-membership filter are pinned to the same 7-day window, so churn cannot inflate the rate.* **Course plus class instance with a dated enrollment bridge** Reusable curriculum, point-in-time denominators, clean teacher attribution, one model that serves both the single-teacher and district views. Cost: two dimensions and a bridge to maintain, one extra join per report. **Single flat classes table** Fewer joins, less to build. Cost: a curriculum change rewrites every class row, and any dropped student is either invisible or double counted, so the exact metric leadership asked for is the one it gets wrong. ## Common follow-up questions - How would you handle a student who transfers between sections of the same course mid-year? _(Tests whether the dated bridge's close-then-reopen pattern handles same-course moves without losing history.)_ - How do you compute the district engagement rollup without double-counting students who sit in multiple classes? _(Tests whether the candidate deduplicates at the student grain before rolling up.)_ - What if impressions arrive at 50k per second per school? _(Tests partitioning fact_engagement_events by event date and separating the high-volume impression stream from reads.)_ - How would you redact PII when a guardian requests a student's data deletion? _(Tests FERPA-style deletion cascades keyed on student_key across the facts and bridge.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/signal_and_silence) - [Data Modeling Interview Questions](https://datadriven.io/data-modeling-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.