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.
Output details
- Count distinct active users with non-null segments as
active_users for each segment-dashboard row.
- Count distinct active users with at least one qualifying January view as
distinct_viewers.
- Count all qualifying January views as
total_views, including multiple views by the same user.
- Calculate
viewer_rate_pct as 100 * distinct_viewers / active_users and round it to two decimal places.
- Use
viewed_at >= DATE '2025-01-01' and viewed_at < DATE '2025-02-01' for January 2025.
- After
user_segment, sort ascending by the dashboard's ascending identifier, not by dashboard_name alone.
