Top 3 Sales Per Month

EasySQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Top 3 Sales Per Month. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

Genpact's reporting team needs a monthly sales leaderboard for management review. Write a PostgreSQL query that calculates completed sales for each sales representative by month and returns the top three representatives in every month.

Requirements

Include only orders with status = 'Completed'.
Aggregate order amounts by calendar month and sales representative.
Rank representatives separately within each month using a window function.
Return the month, representative name, total sales, and rank, ordered chronologically and by rank.

Schema

sales_reps

Column	Type	Description
rep_idPK	INT	Primary key for the sales representative
rep_name	VARCHAR(100)	Representative's full name
region	VARCHAR(50)	Representative's assigned region

orders

Column	Type	Description
order_idPK	INT	Primary key for the order
rep_id	INT	Sales representative assigned to the order
order_date	DATE	Date the order was placed
amount	DECIMAL(12,2)	Order value
status	VARCHAR(20)	Order processing status

Tablessales_repsorders

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results