SQL Window Functions and Spark Equivalence | Dataford Interview Questions

Write SQL queries using RANK, LEAD and LAG with complex joins, then implement the equivalent solutions using Spark DataFrame APIs. Asked in the round 1 stage.

Output

Return one row per completed sale with store_id, store_name, associate_id, associate_name, sale_id, sale_date, amount, total_sales, sales_rank, previous_amount, and next_amount.
Include only completed sales. Rank associates within each store by descending total completed sales, with ties sharing a rank.
Compare sales for the same associate in sale-date and sale-ID order. Sort by store_id, associate_id, sale_date, and sale_id.

Schema

stores

Column	Type	Description
store_idPK	INT	Store identifier
store_name	VARCHAR(100)	Store name

associates

Column	Type	Description
associate_idPK	INT	Associate identifier
associate_name	VARCHAR(100)	Associate name
store_id	INT	Assigned store identifier

sales

Column	Type	Description
sale_idPK	INT	Sale identifier
associate_id	INT	Associate who recorded the sale
sale_date	DATE	Date of the sale
amount	DECIMAL(10,2)	Sale amount
status	VARCHAR(20)	Sale processing status

Your question is SQL Window Functions and Spark Equivalence. Start with the requirements and the three tables in the Question tab.

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

Problem