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
VARIANT 콜론 경로 문법을 통해 RAW.TRIPS_RAW를 직접 쿼리합니다. 이것이 의도적으로 느린 쿼리이며 — 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) ]
[ 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) ]
자동 가져오기(./init_superset.sh) — 그대로 작동합니다. 스크립트는 가져오기 전에.env의 실제 Snowflake 연결을 등록하고, 각 가져오기 후에 올바른 URI를 다시 적용하므로(init_superset.sh의 _update_db 참고) 플레이스홀더 값이 실제 자격 증명으로 덮어써집니다.
Superset UI를 통한 수동 가져오기 — 가져온 데이터베이스는 플레이스홀더 URI로 생성되어 연결되지 않습니다. 가져온 후 Settings → Database Connections → Edit로 이동해 해당 항목의 sqlalchemy_uri를 실제 Snowflake URI로 교체하세요(예: snowflake://<USER>:<PASSWORD>@<ORG>-<ACCOUNT>/NYC_TAXI_DB/ANALYTICS?role=ANALYST_ROLE&warehouse=ANALYTICS_WH).
직접 만든 대시보드를 다시 내보내기 — Superset은 내보낼 때 databases/*.yaml에 여러분의 계정 로케이터와 사용자명을 그대로 넣습니다. 다시 내보낸 ZIP을 커밋하기 전에 그 값들을 MYORG-MYACCOUNT / LAB_USER로 되돌려 마스킹해서 계정 식별자가 git 히스토리로 유출되지 않게 하세요.
