Scenario

You are a Data Analyst at an amusement park and need to identify rides whose average visitor rating deviates significantly from the norm.

Task

Write a Snowflake SQL query that:

1. Joins {{ ref("rides") }} with {{ ref("visitors") }} on ride_id using an inner join (rides with no visitor ratings should be excluded)
2. Computes the average rating per ride (rounded to 2 decimal places)
3. Computes the global mean and standard deviation of all rides' average ratings using window functions
4. Flags each ride as anomalous when its average rating deviates from the global mean by more than one standard deviation
5. Returns ride_id, ride_name, average_rating, and is_anomalous

Schema

rides

visitors

Example

rides:

visitors:

Expected Output:

Note: R005 (Gentle River) has no visitor ratings and is excluded. R003 (Splash Falls) has an average rating of 1.0, which deviates from the global mean (3.375) by more than one standard deviation (1.797), so it is flagged as anomalous.