Analytics Dashboard Queries

Introduction

Dashboards need fast aggregates over big tables — DAU/MAU, revenue, retention, funnels. Materialized views, indexes, and warehouse-grade SQL keep them snappy.

Understanding the topic

Core concepts to understand:

• Time-bucketed counts: date_trunc.
• Cohort analysis: GROUP BY signup-month + active-month.
• Funnels: SUM(CASE WHEN step=...) per step.
• Pre-aggregate via materialized views.
• Use a separate replica or warehouse for analytics.

Syntax reference

Visual workflow / architecture:

events  ──▶  hourly aggregates (MV)  ──▶  Dashboard
                                     │
                                     ▼
                                 daily/weekly rollups

Real-world use

Mixpanel, Amplitude, Heap and every internal BI tool run on aggregations of huge event tables, often partitioned by date.

Best practices

• Pre-aggregate hot dashboards.
• Time-partition raw events.
• Run analytics on a replica/warehouse.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. DAU/MAU SQL.
• Q2. Cohort retention query.
• Q3. Why use materialized views?
• Q4. Warehouse vs OLTP DB.
• Q5. Funnel SQL pattern.