{"queries":[{"id":"date_bounds","compiledQueryString":"SELECT detected_at FROM birdbox.fct_bird_detections","inputQueryString":"SELECT detected_at FROM birdbox.fct_bird_detections","compiled":false,"inline":true},{"id":"fct_bird_detections_summary","compiledQueryString":"SELECT common_name AS species, count(*) AS detections \nFROM birdbox.fct_bird_detections \nWHERE detected_at::DATE >= '${inputs.date_filter.start}'::DATE\n AND detected_at::DATE <= '${inputs.date_filter.end}'::DATE\nGROUP BY common_name \nORDER BY detections DESC \nLIMIT 15","inputQueryString":"SELECT common_name AS species, count(*) AS detections \nFROM birdbox.fct_bird_detections \nWHERE detected_at::DATE >= '${inputs.date_filter.start}'::DATE\n AND detected_at::DATE <= '${inputs.date_filter.end}'::DATE\nGROUP BY common_name \nORDER BY detections DESC \nLIMIT 15","compiled":false,"inline":true},{"id":"fct_bird_detections_by_hour","compiledQueryString":"SELECT \n EXTRACT(HOUR FROM detected_at) AS hour_of_day,\n count(*) AS detections\nFROM birdbox.fct_bird_detections \nWHERE detected_at::DATE >= '${inputs.date_filter.start}'::DATE\n AND detected_at::DATE <= '${inputs.date_filter.end}'::DATE\nGROUP BY hour_of_day\nORDER BY hour_of_day","inputQueryString":"SELECT \n EXTRACT(HOUR FROM detected_at) AS hour_of_day,\n count(*) AS detections\nFROM birdbox.fct_bird_detections \nWHERE detected_at::DATE >= '${inputs.date_filter.start}'::DATE\n AND detected_at::DATE <= '${inputs.date_filter.end}'::DATE\nGROUP BY hour_of_day\nORDER BY hour_of_day","compiled":false,"inline":true},{"id":"fct_species_hour_heatmap","compiledQueryString":"WITH top_species AS (\n SELECT common_name\n FROM birdbox.fct_bird_detections\n WHERE detected_at::DATE >= '${inputs.date_filter.start}'::DATE\n AND detected_at::DATE <= '${inputs.date_filter.end}'::DATE\n GROUP BY common_name\n ORDER BY count(*) DESC\n LIMIT 10\n)\nSELECT \n d.common_name AS species,\n LPAD(CAST(EXTRACT(HOUR FROM d.detected_at) AS VARCHAR), 2, '0') AS hour_of_day,\n count(*) AS detections\nFROM birdbox.fct_bird_detections d\nWHERE d.detected_at::DATE >= '${inputs.date_filter.start}'::DATE\n AND d.detected_at::DATE <= '${inputs.date_filter.end}'::DATE\n AND d.common_name IN (SELECT common_name FROM top_species)\nGROUP BY d.common_name, hour_of_day\nORDER BY hour_of_day, d.common_name","inputQueryString":"WITH top_species AS (\n SELECT common_name\n FROM birdbox.fct_bird_detections\n WHERE detected_at::DATE >= '${inputs.date_filter.start}'::DATE\n AND detected_at::DATE <= '${inputs.date_filter.end}'::DATE\n GROUP BY common_name\n ORDER BY count(*) DESC\n LIMIT 10\n)\nSELECT \n d.common_name AS species,\n LPAD(CAST(EXTRACT(HOUR FROM d.detected_at) AS VARCHAR), 2, '0') AS hour_of_day,\n count(*) AS detections\nFROM birdbox.fct_bird_detections d\nWHERE d.detected_at::DATE >= '${inputs.date_filter.start}'::DATE\n AND d.detected_at::DATE <= '${inputs.date_filter.end}'::DATE\n AND d.common_name IN (SELECT common_name FROM top_species)\nGROUP BY d.common_name, hour_of_day\nORDER BY hour_of_day, d.common_name","compiled":false,"inline":true},{"id":"fct_weather_scatter","compiledQueryString":"SELECT temperature_f, humidity_pct, common_name, detected_at\nFROM birdbox.fct_detections_with_weather\nWHERE temperature_f IS NOT NULL\n AND detected_at::DATE >= '${inputs.date_filter.start}'::DATE\n AND detected_at::DATE <= '${inputs.date_filter.end}'::DATE","inputQueryString":"SELECT temperature_f, humidity_pct, common_name, detected_at\nFROM birdbox.fct_detections_with_weather\nWHERE temperature_f IS NOT NULL\n AND detected_at::DATE >= '${inputs.date_filter.start}'::DATE\n AND detected_at::DATE <= '${inputs.date_filter.end}'::DATE","compiled":false,"inline":true},{"id":"fct_new_arrivals","compiledQueryString":"SELECT scientific_name, detected_at, confidence_score, alert_message\nFROM birdbox.fct_daily_alerts\nORDER BY detected_at DESC\nLIMIT 5","inputQueryString":"SELECT scientific_name, detected_at, confidence_score, alert_message\nFROM birdbox.fct_daily_alerts\nORDER BY detected_at DESC\nLIMIT 5","compiled":false,"inline":true},{"id":"fct_confidence_breakdown","compiledQueryString":"SELECT confidence_tier, count(*) AS detections\nFROM birdbox.fct_bird_detections\nWHERE detected_at::DATE >= '${inputs.date_filter.start}'::DATE\n AND detected_at::DATE <= '${inputs.date_filter.end}'::DATE\nGROUP BY confidence_tier\nORDER BY CASE confidence_tier WHEN 'high' THEN 1 WHEN 'medium' THEN 2 ELSE 3 END","inputQueryString":"SELECT confidence_tier, count(*) AS detections\nFROM birdbox.fct_bird_detections\nWHERE detected_at::DATE >= '${inputs.date_filter.start}'::DATE\n AND detected_at::DATE <= '${inputs.date_filter.end}'::DATE\nGROUP BY confidence_tier\nORDER BY CASE confidence_tier WHEN 'high' THEN 1 WHEN 'medium' THEN 2 ELSE 3 END","compiled":false,"inline":true},{"id":"fct_bird_detections","compiledQueryString":"SELECT strftime(detected_at, '%Y-%m-%d %I:%M:%S %p') AS \"Timestamp\", common_name AS \"Common Name\", scientific_name AS \"Scientific Name\", confidence_tier AS \"Confidence Tier\" FROM birdbox.fct_bird_detections ORDER BY detected_at DESC","inputQueryString":"SELECT strftime(detected_at, '%Y-%m-%d %I:%M:%S %p') AS \"Timestamp\", common_name AS \"Common Name\", scientific_name AS \"Scientific Name\", confidence_tier AS \"Confidence Tier\" FROM birdbox.fct_bird_detections ORDER BY detected_at DESC","compiled":false,"inline":true}]}

No Results