Write SQL using joins and window functions

Quick Overview

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.

Company: Capital One

Role: Data Engineer

Category: Coding & Algorithms

Difficulty: medium

Interview Round: Technical Screen

## 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`.

Overview: 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.

You are given two in-memory tables: accounts and transactions. Each account belongs to a customer and a segment. Each transaction belongs to an account. For each segment, find the customers with the top 3 SQL-style RANK values by total transaction amount in the last 30 days, inclusive of both the cutoff timestamp and current timestamp. A customer may have multiple accounts, and all qualifying transactions from all of their accounts in the same segment must be included. Only transactions whose account_id exists in accounts should be considered. Return results ordered by segment, then rank_in_segment, then customer_id. Ranking must behave like SQL RANK(): tied totals receive the same rank, and the next rank skips accordingly. Include all customers whose rank is at most 3, including ties.

Constraints

0 <= len(accounts) <= 100000
0 <= len(transactions) <= 100000
account_id values in accounts are unique
txn_ts and current_ts are ISO timestamp strings without time zones
amount is numeric and may be positive, zero, or negative
Only customers with at least one qualifying transaction in the last 30 days are returned

Examples

Input: ([{'account_id': 'a1', 'customer_id': 'c1', 'segment': 'prime'}, {'account_id': 'a2', 'customer_id': 'c1', 'segment': 'prime'}, {'account_id': 'a3', 'customer_id': 'c2', 'segment': 'prime'}, {'account_id': 'a4', 'customer_id': 'c3', 'segment': 'prime'}, {'account_id': 'a5', 'customer_id': 'c4', 'segment': 'prime'}, {'account_id': 'a6', 'customer_id': 'c5', 'segment': 'subprime'}], [{'transaction_id': 't1', 'account_id': 'a1', 'amount': 100, 'txn_ts': '2025-01-10 00:00:00'}, {'transaction_id': 't2', 'account_id': 'a2', 'amount': 50, 'txn_ts': '2025-01-20 00:00:00'}, {'transaction_id': 't3', 'account_id': 'a3', 'amount': 200, 'txn_ts': '2025-01-15 00:00:00'}, {'transaction_id': 't4', 'account_id': 'a4', 'amount': 75, 'txn_ts': '2025-01-30 00:00:00'}, {'transaction_id': 't5', 'account_id': 'a4', 'amount': 25, 'txn_ts': '2024-12-31 23:59:59'}, {'transaction_id': 't6', 'account_id': 'a5', 'amount': 75, 'txn_ts': '2025-01-05 00:00:00'}, {'transaction_id': 't7', 'account_id': 'a6', 'amount': 300, 'txn_ts': '2025-01-02 00:00:00'}], '2025-01-31 00:00:00')

Expected Output: [('prime', 'c2', 200, 1), ('prime', 'c1', 150, 2), ('prime', 'c3', 75, 3), ('prime', 'c4', 75, 3), ('subprime', 'c5', 300, 1)]

Explanation: Customer c1 has two prime accounts totaling 150. Customers c3 and c4 tie at rank 3 in prime, so both are included. The old transaction on 2024-12-31 is outside the 30-day window.

Input: ([{'account_id': 'a1', 'customer_id': 'c1', 'segment': 'prime'}, {'account_id': 'a2', 'customer_id': 'c2', 'segment': 'prime'}, {'account_id': 'a3', 'customer_id': 'c3', 'segment': 'prime'}, {'account_id': 'a4', 'customer_id': 'c4', 'segment': 'prime'}], [{'transaction_id': 't1', 'account_id': 'a1', 'amount': 10, 'txn_ts': '2025-01-02 12:00:00'}, {'transaction_id': 't2', 'account_id': 'a2', 'amount': 20, 'txn_ts': '2025-02-01 12:00:00'}, {'transaction_id': 't3', 'account_id': 'a3', 'amount': 100, 'txn_ts': '2025-01-02 11:59:59'}, {'transaction_id': 't4', 'account_id': 'a4', 'amount': 1000, 'txn_ts': '2025-02-01 12:00:01'}, {'transaction_id': 't5', 'account_id': 'missing', 'amount': 999, 'txn_ts': '2025-02-01 12:00:00'}], '2025-02-01 12:00:00')

Expected Output: [('prime', 'c2', 20, 1), ('prime', 'c1', 10, 2)]

Explanation: The transaction exactly 30 days before current_ts and the transaction exactly at current_ts are included. The older transaction, future transaction, and transaction with no matching account are ignored.

Hints

Build a mapping from account_id to its customer_id and segment before processing transactions.
After aggregating totals by (segment, customer_id), sort each segment by total descending and assign SQL-style RANK values.

Community answers

Answer by mkrishna.parimi

from datetime import datetime, timedelta def solution(accounts, transactions, current_ts): timestamp_format = '%Y-%m-%d %H:%M:%S' current_ts = datetime.strptime( current_ts, timestamp_format ) cutoff_dt = current_ts - timedelta(days=30) account_lookup = {} totals = {} results = {} for account in accounts: account_id = account['account_id'] customer_id = account['customer_id'] segment = account['segment'] account_lookup[account_id] = (customer_id, segment) for txn in transactions: account_id = txn['account_id'] account_info = account_lookup.get(account_id) if account_info is None: continue txn_dt = datetime.strptime( txn['txn_ts'], timestamp_format ) if not (cutoff_dt <= txn_dt <= current_ts): continue customer_id, segment = account_info amount = txn['amount'] if segment not in totals: totals[segment] = {} totals[segment][customer_id] = ( totals[segment].get(customer_id, 0) + amount ) print(f"total:{totals}") for segment, customer_totals in totals.items(): sorted_customer_totals = sorted(customer_totals.items(), key = lambda x: (-x[1], x[0])) previous_total = None rank = 0 results[segment] = [] for position, (customer_id, total) in enumerate(sorted_customer_totals, start=1): if total != previous_total: rank = position if rank <=3: results[segment].append((customer_id, total, rank)) else: break previous_total = total sorted_results = [ (segment, customer_id, total, rank) for segment, values in results.items() for customer_id, total, rank in values ] return sorted_results

Loading coding console...