3 hints available

EasyData EngineeringCodingSQL

Window Functions for Order Analytics

You have a table orders with columns: order_id, customer_id, order_date, order_total.

Write SQL using window functions to answer:

(1) For each customer, compute the value of their most recent order and their second-most-recent order side by side. Show the columns and explain which window function you use.

(2) Compute the 30-day rolling average order value across all customers for each day. Specify the frame clause precisely.

(3) Identify customers who placed orders in the top 10% by value within their first 90 days as a customer. (Hint: this requires a partitioned percentile rank.)

(4) Explain why each of the above queries is significantly easier with window functions than without — what would the GROUP BY or self-join equivalent look like?

Available Tables
orders
order_id INTEGER
customer_id INTEGER
order_date DATE
order_total DECIMAL(10,2)

Sign in to attempt this problem

Free account gives you full access to community problems with the complete solution reveal: golden answer, senior walkthrough, and score breakdown, after submission.

Start free →Already have an account? Sign in