Write SQL using joins and window functions

Q: Write SQL using joins and window functions

This question evaluates proficiency in SQL joins, aggregation, and window functions for ranked group-level analytics, specifically testing the ability to compute partitioned ranks, handle ties, and apply time-based filters in a data engineering context.

Q: How do I approach Coding & Algorithms interview questions?

Coding & Algorithms questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master coding & algorithms interviews.

Question

SQL Coding: Rank within Groups with Joins

You are given two tables:

`accounts`

account_id (string)
customer_id (string)
segment (string) — e.g., "prime" , "subprime"

`transactions`

transaction_id (string)
account_id (string)
amount (numeric)
txn_ts (timestamp)

Task

For each segment, find the top 3 customers by total transaction amount in the last 30 days (inclusive).

Requirements:

A customer may have multiple accounts ; include all their accounts’ transactions.
Use appropriate JOINs to connect transactions to segments.
Use a window function to compute the rank within each segment .
If there are ties at rank 3, include all tied customers.

Output

Return columns:

segment
customer_id
total_amount_30d
rank_in_segment

Order results by segment, then rank_in_segment, then customer_id.