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
Mengquery RAW.TRIPS_RAW secara langsung melalui sintaks colon-path VARIANT. Ini adalah query yang sengaja dibuat lambat — target benchmark ClickHouse.
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) ]
Tujuan: Tampilan strategis untuk tinjauan bisnis mingguan. Menampilkan window function dan sintaks khusus Snowflake (QUALIFY) yang memerlukan penulisan ulang di ClickHouse.
Tujuan: Penyelaman mendalam pada performa driver dan kualitas trip. Sengaja menjadi dashboard paling lambat — mengquery RAW.TRIPS_RAW secara langsung melalui akses VARIANT. Catat waktu query di sini sebagai baseline untuk benchmark performa ClickHouse di Bagian 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) ]
Impor otomatis (./init_superset.sh) — bekerja apa adanya. Skrip mendaftarkan koneksi Snowflake yang sebenarnya dari .envsebelum mengimpor, lalu menerapkan ulang URI yang benar setelah setiap impor (lihat _update_db di init_superset.sh), sehingga nilai placeholder ditimpa dengan kredensial Anda yang sebenarnya.
Impor manual melalui UI Superset — database yang diimpor akan dibuat dengan URI placeholder dan tidak akan terhubung. Setelah impor, buka Settings → Database Connections → Edit pada entri tersebut dan ganti sqlalchemy_uri dengan URI Snowflake Anda yang sebenarnya (mis. snowflake://<USER>:<PASSWORD>@<ORG>-<ACCOUNT>/NYC_TAXI_DB/ANALYTICS?role=ANALYST_ROLE&warehouse=ANALYTICS_WH).
Mengekspor ulang dashboard Anda sendiri — Superset menanamkan account locator dan username Anda ke dalam databases/*.yaml saat ekspor. Sebelum meng-commit ZIP hasil ekspor ulang Anda, sunting nilai-nilai tersebut kembali menjadi MYORG-MYACCOUNT / LAB_USER agar identifier akun Anda tidak bocor ke riwayat git.
