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.

What difficulty level is this interview question?

This is a medium difficulty Coding & Algorithms question, commonly asked during Technical Screen rounds at Capital One.

What role is this question designed for?

This question is commonly asked for Data Engineer candidates at Capital One during technical interviews.

Write SQL using joins and window functions

Last updated: Apr 30, 2026

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.

Capital One

Mar 1, 2026, 12:00 AM

Data Engineer

Technical Screen

Coding & Algorithms

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.

Submit Your Answer

Loading comments...

Browse More Questions

More Coding & Algorithms•More Capital One•More Data Engineer•Capital One Data Engineer•Capital One Coding & Algorithms•Data Engineer Coding & Algorithms