Given the table temps with columns day and temp, what will be the output of this query?

medium📝 query result Q4 of 15

SQL - Advanced Window Functions

Given the table temps with columns day and temp, what will be the output of this query?

SELECT day, temp, AVG(temp) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg FROM temps ORDER BY day;

Assuming data:
day: 1, 2, 3
temp: 10, 20, 30

Aday=1, moving_avg=10; day=2, moving_avg=20; day=3, moving_avg=25

Bday=1, moving_avg=10; day=2, moving_avg=20; day=3, moving_avg=30

Cday=1, moving_avg=10; day=2, moving_avg=15; day=3, moving_avg=20

Dday=1, moving_avg=10; day=2, moving_avg=15; day=3, moving_avg=25

Step-by-Step Solution

Solution:

Step 1: Understand the window frame
The frame includes the current row and the previous row (1 preceding to current).
Step 2: Calculate moving averages row by row
Day 1: only one row (10), average = 10
Day 2: rows with temp 10 and 20, average = (10+20)/2 = 15
Day 3: rows with temp 20 and 30, average = (20+30)/2 = 25
Final Answer:
day=1, moving_avg=10; day=2, moving_avg=15; day=3, moving_avg=25 -> Option D
Quick Check:
Moving average includes current + 1 preceding row [OK]

Quick Trick: Calculate averages using current and previous row values [OK]

Common Mistakes:

Master "Advanced Window Functions" in SQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free

More SQL Quizzes