Window Functions vs GROUP BY — Concept Check
Questions on Window Functions vs GROUP BY. GROUP BY collapses many rows into one aggregate row per group; window functions keep all rows while adding computed values — choose based on whether you need row-level detail alongside aggregates.
Questions in this quiz
8 questions on Window Functions vs GROUP BY — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.A report requires listing each employee's salary alongside the department's average salary. Which SQL approach should you use?
- AUse a window function to calculate department average while preserving individual salaries
- BUse GROUP BY to aggregate salaries and calculate department average
- CUse a subquery to calculate department average and join with employee details
- DUse GROUP BY to aggregate salaries by department and join with employee details
2.You need to display each customer's total orders along with each order's details. What SQL construct is appropriate?
- AUse a subquery to calculate total orders and join with order details
- BGROUP BY on customer ID and join with order details
- CGROUP BY on order ID and aggregate customer orders
- DUse a window function partitioned by customer ID
3.A query lists each department's average salary compared to the company average. Which SQL feature allows this?
- AGROUP BY with a subquery for company average
- BGROUP BY with a join on company average
- CWindow function over the grouped department results
- DCTE to calculate department and company averages
4.Why can't window function results be used directly in a WHERE clause?
- AWindow functions are only for aggregation
- BWindow functions require a GROUP BY clause
- CWindow functions execute before WHERE clause
- DWindow functions execute after WHERE clause
5.A developer uses GROUP BY to list each order's details with total orders per customer. What is the issue?
- AGROUP BY will not aggregate total orders correctly
- BGROUP BY will lose individual order details
- CGROUP BY needs a join with customer data
- DGROUP BY requires a window function for totals
6.What is a common mistake when trying to filter results based on window function calculations?
- AUsing window function results in WHERE clause
- BUsing window functions in SELECT clause
- CUsing GROUP BY before window functions
- DUsing window functions without PARTITION BY
7.An analyst needs to show each order's amount and its percentage of total customer spending. What SQL feature is suitable?
- AGROUP BY with a join on total spending
- BCTE to calculate total spending and join with orders
- CWindow function to calculate total and percentage
- DGROUP BY with a subquery for total spending
8.Which SQL construct allows comparing each department's average salary to the overall company average in a single query?
- ACTE with GROUP BY
- BJOIN with aggregated results
- CGROUP BY with a window function
- DSubquery with GROUP BY