Quick Overview

Write SQL using joins and a window function to find the top three customers by transaction total in each segment over the last 30 days, inclusive, using account and transaction tables. Include transactions across all of a customer’s accounts and everyone tied at rank three, ordered by segment, rank, and customer ID.

Write SQL using joins and window functions

Company: Capital One

Role: Data Scientist

Category: Coding & Algorithms

Difficulty: medium

Interview Round: Onsite

## 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: Write SQL using joins and a window function to find the top three customers by transaction total in each segment over the last 30 days, inclusive, using account and transaction tables. Include transactions across all of a customer’s accounts and everyone tied at rank three, ordered by segment, rank, and customer ID.

Read the full Capital One Data Scientist interview experience this question came from

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

  1. Build a mapping from account_id to its customer_id and segment before processing transactions.
  2. 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...