SELECT DATE_TRUNC('hour', pickup_at) AS hour_bucket, pickup_borough, COUNT(*) AS trip_count, SUM(total_amount_usd) AS total_revenue, AVG(tip_amount_usd / NULLIF(fare_amount_usd, 0)) AS avg_tip_rate, AVG(trip_distance_miles) AS avg_distance_milesFROM ANALYTICS.FACT_TRIPSWHERE pickup_at >= DATEADD('day', -7, CURRENT_TIMESTAMP()) AND pickup_borough IS NOT NULLGROUP BY 1, 2ORDER BY 1 DESC, total_revenue DESC
SELECT pickup_at::DATE AS trip_date, COUNT(*) AS daily_trip_count, AVG(trip_distance_miles) AS daily_avg_distance, AVG(AVG(trip_distance_miles)) OVER ( ORDER BY pickup_at::DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS rolling_7d_avg_distance, SUM(total_amount_usd) AS daily_revenue, SUM(SUM(total_amount_usd)) OVER ( ORDER BY pickup_at::DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS rolling_7d_revenueFROM ANALYTICS.FACT_TRIPSGROUP BY 1ORDER BY 1 DESCLIMIT 365
SELECT CASE WHEN surge_multiplier >= 2.0 THEN 'High Surge (2x+)' WHEN surge_multiplier >= 1.5 THEN 'Medium Surge (1.5–2x)' WHEN surge_multiplier > 1.0 THEN 'Low Surge (1–1.5x)' ELSE 'No Surge (1x)' END AS surge_category, COUNT(*) AS trip_count, ROUND(AVG(total_amount_usd), 2) AS avg_total_fare, ROUND(AVG(fare_amount_usd), 2) AS avg_base_fare, ROUND(AVG(surge_multiplier), 2) AS avg_surgeFROM ANALYTICS.FACT_TRIPSWHERE surge_multiplier IS NOT NULLGROUP BY 1ORDER BY avg_surge DESC
Schema: RAW ← change this when creating the dataset
Queries RAW.TRIPS_RAW directly via VARIANT colon-path syntax. This is the intentionally slow query — the ClickHouse benchmark target.
SELECT ROUND(TRIP_METADATA:driver.rating::FLOAT, 1) AS rating_bucket, COUNT(*) AS trip_count, ROUND(AVG(TOTAL_AMOUNT), 2) AS avg_fare, ROUND(AVG(DATEDIFF('minute', PICKUP_DATETIME, DROPOFF_DATETIME)), 1) AS avg_duration_minutesFROM RAW.TRIPS_RAWWHERE TRIP_METADATA:driver IS NOT NULL AND TRIP_METADATA:driver.rating IS NOT NULLGROUP BY 1ORDER BY 1
SELECT vehicle_type, COUNT(*) AS trip_count, SUM(total_amount_usd) AS total_revenue, AVG(total_amount_usd) AS avg_fare, AVG(trip_distance_miles) AS avg_distanceFROM ANALYTICS.FACT_TRIPSWHERE vehicle_type IS NOT NULLGROUP BY 1ORDER BY total_revenue DESC
SELECT traffic_level, COUNT(*) AS trip_count, AVG(duration_minutes) AS avg_duration_minutes, AVG(trip_distance_miles) AS avg_distance_miles, AVG(total_amount_usd) AS avg_fareFROM ANALYTICS.FACT_TRIPSWHERE traffic_level IS NOT NULLGROUP BY 1ORDER BY avg_duration_minutes DESC
SELECT pickup_at::DATE AS trip_date, app_platform, COUNT(*) AS trip_count, AVG(surge_multiplier) AS avg_surgeFROM ANALYTICS.FACT_TRIPSWHERE app_platform IS NOT NULL AND pickup_at >= DATEADD('day', -30, CURRENT_TIMESTAMP())GROUP BY 1, 2ORDER BY 1 DESC
[ Total Trips — Big Number ] [ Total Revenue — Big Number (add 2nd) ][ Trips per Hour — Line chart (full width) ][ Revenue by Borough — Bar ] [ Payment Type Split — Pie ][ Borough Performance — Table (full width) ]
Purpose: Strategic view for weekly business review. Showcases window functions and Snowflake-specific syntax (QUALIFY) that require rewrites in ClickHouse.
Purpose: Deep-dive on driver performance and trip quality. Intentionally the slowest dashboard — queries RAW.TRIPS_RAW directly via VARIANT access. Record the query time here as the baseline for the ClickHouse performance benchmark in Part 2.
[ Trip Count by Rating — Bar ] [ Avg Fare by Rating — Line ][ Revenue by Vehicle Type — Horizontal bar (full width) ][ Traffic Level Impact — Bar (50%) ] [ Surge by Platform — Table (50%)][ Daily Trips by Platform — Line chart (full width) ]
Auto-import (./init_superset.sh) — works as-is. The script registers the real Snowflake connection from .envbefore importing, then re-applies the correct URI after each import (see _update_db in init_superset.sh), so the placeholder values are overwritten with your real credentials.
Manual import via Superset UI — the imported database will be created with the placeholder URI and won't connect. After import, go to Settings → Database Connections → Edit the entry and replace sqlalchemy_uri with your real Snowflake URI (e.g. snowflake://<USER>:<PASSWORD>@<ORG>-<ACCOUNT>/NYC_TAXI_DB/ANALYTICS?role=ANALYST_ROLE&warehouse=ANALYTICS_WH).
Re-exporting your own dashboards — Superset bakes your account locator and username into databases/*.yaml on export. Before committing your re-exported ZIPs, redact those values back to MYORG-MYACCOUNT / LAB_USER so your account identifiers don't leak into git history.
