Rolling Averages and Window Functions: SQL Skills Quiz Quiz

1. Understanding Rolling Averages

   What is the main purpose of calculating a rolling average in a time-series dataset?

   1. To examine data trends within a specific recent window of time
   2. To calculate the total sum of all records
   3. To select unique values from the data
   4. To remove duplicate records from results
   5. To randomly sample from the dataset

2. Window Function Syntax

   Which of the following SQL window function clauses accurately defines a 3-day rolling window including the current row?

   1. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
   2. ROWS BETWEEN CURRENT ROW AND 2 PRECEDING
   3. ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
   4. ROWS BETWEEN CURRENT ROW AND 3 PRECEDING
   5. ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

3. Selecting Columns for Output

   If you want to output each user's ID, the tweet date, and their 3-day rolling tweet average, which of the following column lists is correct?

   1. user_id, tweet_date, rolling_average
   2. username, date, tweet_count
   3. user_id, tweet_avg, tweet_date
   4. userid, date, rolling_mean
   5. user, tweet_date, average

4. Achieving Partitioned Calculations

   To compute rolling averages for each user separately, which clause is essential in your window function?

   1. PARTITION BY user_id
   2. GROUP BY tweet_date
   3. ORDER BY tweet_count
   4. HAVING user_id IS NOT NULL
   5. DISTINCT ON user_id

5. Ordering for Correct Rolling Results

   When defining a rolling window in SQL, what is the correct column to ORDER BY in the window function?

   1. The date or timestamp column
   2. The user ID column
   3. The tweet count column
   4. The rolling average column
   5. No ordering is needed

6. Syntax Check: AVG with Window

   Which is the correct way to use AVG in a window function to calculate a rolling mean over the last 3 rows?

   1. AVG(tweet_count) OVER (PARTITION BY user_id ORDER BY tweet_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
   2. SUM(tweet_count) PARTITION BY user_id ORDER tweet_date RANGE 2 PRECEDING AND CURRENT
   3. AVG(tweet_count) FROM (GROUP BY user_id)
   4. COUNT(tweet_count) WINDOW user_id ORDER BY tweet_date ROWS BETWEEN 2 PRECEDING
   5. AVG tweet_count OVER (ORDER BY tweet_date GROUP BY user_id)

7. Rounding Averaged Results

   What SQL function can you use to round the rolling average to 2 decimal places?

   1. ROUND(average_value, 2)
   2. TRUNCATE(average_value, 2)
   3. ROUNDUP(average_value, 2)
   4. FORMAT(2, average_value)
   5. FLOOR(average_value, 2)

8. Clarifying Terminology

   What is another commonly used term for 'rolling average' in data analysis?

   1. Moving average
   2. Central tendency
   3. Aggregate mean
   4. Grouped mean
   5. Relative average

9. Error Spotting: Typos in SQL

   Identify the typo in this SQL clause: 'ROWS BTWEEN 2 PRECEDING AND CURRENT ROW'

   1. 'BTWEEN' should be 'BETWEEN'
   2. 'CURRENT ROW' should be 'CURRENT'
   3. 'PRECEDING' should be 'PRECEEDING'
   4. 'ROWS' should be 'ROWES'
   5. 'AND' should be 'ADD'

10. Aggregating for Each User

    Why is it necessary to use PARTITION BY when calculating a rolling average for every user in a tweet table?

    1. So each user's records are treated as separate groups during calculation
    2. To join two tables by user name
    3. To improve query performance
    4. To limit the results to a single user
    5. To remove duplicate users from output