Dashboard Design for User Segments | Dataford Interview Questions

How do you approach designing dashboards for different user segments at The Johns Hopkins University?

For a SQL implementation exercise, translate that requirement into a usage summary for active users during January 2025. Include dashboards intended for a user's segment or for all segments, including dashboards with no views.

Output

One row per eligible user segment and dashboard.
Columns: user_segment, dashboard_name, active_users, distinct_viewers, total_views, and viewer_rate_pct.
Include only active users with a non-null segment and January 2025 views when applicable. Sort by user_segment, then dashboard_name.

Schema

university_users

Column	Type	Description
user_idPK	INT	Unique university user identifier
user_segment	VARCHAR(50)	User audience segment
is_active	BOOLEAN	Whether the user is active

dashboards

Column	Type	Description
dashboard_idPK	INT	Unique dashboard identifier
dashboard_name	VARCHAR(100)	Dashboard display name
audience_segment	VARCHAR(50)	Intended user segment, or All

dashboard_views

Column	Type	Description
view_idPK	INT	Unique dashboard view identifier
user_id	INT	User who viewed the dashboard
dashboard_id	INT	Dashboard that was viewed
viewed_at	TIMESTAMP	Timestamp of the dashboard view

Your question is Dashboard Design for User Segments. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem