SUM, AVG, COUNT as window functions in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the running total of sales using a window function.

PostgreSQL

SELECT sales, [1](sales) OVER (ORDER BY date) AS running_total FROM sales_data;

Drag options to blanks, or click blank then click option'

ASUM

BAVG

CCOUNT

DMAX

Attempts:

3 left

💡 Hint

Common Mistakes

Using AVG or COUNT instead of SUM for running total.

Forgetting the OVER clause.

The SUM function calculates the running total of sales when used as a window function with an ORDER BY clause.

2fill in blank

medium

Complete the code to calculate the average sales per department using a window function.

PostgreSQL

SELECT department, sales, AVG(sales) OVER (PARTITION BY [1]) AS avg_sales FROM sales_data;

Drag options to blanks, or click blank then click option'

Adepartment

Bdate

Csales

Dregion

Attempts:

3 left

💡 Hint

Common Mistakes

Partitioning by sales or date instead of department.

Omitting the PARTITION BY clause.

The PARTITION BY department clause groups rows by department to calculate the average sales per department.

3fill in blank

hard

Fix the error in the code to count the number of sales entries per region using a window function.

PostgreSQL

SELECT region, sales, COUNT([1]) OVER (PARTITION BY region) AS sales_count FROM sales_data;

Drag options to blanks, or click blank then click option'

Adate

Bsales

C*

Dregion

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT(sales) which ignores NULL sales values.

Using COUNT(region) which is redundant inside PARTITION BY region.

Using COUNT(*) counts all rows per region regardless of NULLs in any column.

4fill in blank

hard

Fill both blanks to calculate the cumulative average sales ordered by date within each region.

PostgreSQL

SELECT region, date, sales, AVG(sales) OVER (PARTITION BY [1] ORDER BY [2] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_avg FROM sales_data;

Drag options to blanks, or click blank then click option'

Aregion

Bdate

Csales

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping partition and order columns.

Using sales or department in partition or order incorrectly.

Partition by region and order by date to calculate cumulative average sales over time within each region.

5fill in blank

hard

Fill all three blanks to calculate the count of sales, sum of sales, and average sales per department.

PostgreSQL

SELECT department, COUNT([1]) OVER (PARTITION BY department) AS sales_count, SUM([2]) OVER (PARTITION BY department) AS sales_sum, AVG([3]) OVER (PARTITION BY department) AS sales_avg FROM sales_data;

Drag options to blanks, or click blank then click option'

A*

Bsales

Cdepartment

Ddate

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT(sales) which ignores NULL sales.

Using department or date instead of sales for SUM and AVG.

Use COUNT(*) to count all rows, and SUM(sales) and AVG(sales) to calculate sum and average of sales per department.