Katkat
Back

Write SQL using HAVING and window functions

PayPalData Scientist · Technical Screen
EasyData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This question tests the ability to filter aggregated data using the HAVING clause and compute relative metrics using window functions to identify high-frequency transaction patterns.

Fraud Detection Analytics

You are working with a transactions table that logs payments made by users. Your goal is to identify "high-activity" users who have demonstrated potential risk.

Schema

Table transactions:

  • user_id: INT
  • amount: DECIMAL
  • transaction_time: TIMESTAMP

Requirements

  1. Identify users who have completed more than 5 transactions in total.
  2. For these users, calculate their "transaction rank" for each day based on the amount spent (highest amount = rank 1). If two transactions have the same amount, assign them the same rank and skip the next rank (e.g., 1, 2, 2, 4).
  3. Return a table containing user_id, transaction_time, amount, and the calculated day_rank.

Constraints

  • Use standard SQL syntax (PostgreSQL/BigQuery compatible).
  • Ensure the filter for "more than 5 transactions" is applied before or during the result generation.

Example

Input:

user_idamounttransaction_time
11002023-01-01 10:00:00
12002023-01-01 11:00:00
11002023-01-01 12:00:00

Output (if user 1 had > 5 total transactions):

user_idtransaction_timeamountday_rank
12023-01-01 10:00:001002
12023-01-01 11:00:002001
12023-01-01 12:00:001002

Hints

Hint 1

Use a subquery or a Common Table Expression (CTE) with a GROUP BY and HAVING count(*) > 5 clause to filter the list of active users.

Hint 2

Use the DENSE_RANK() window function partition by user_id and the date portion of the timestamp to calculate the daily ranking.

Hint 3

Ensure the final query joins your list of high-activity users back to the original transaction table to fetch the specific records.

Follow-up questions

  • How would your query change if you needed to identify the top 3 transactions per user per day regardless of their total transaction count?
  • What are the performance implications of using a window function on a table with 100 million rows compared to a self-join approach?
  • How would you modify the ranking logic to use a dense rank if multiple transactions on the same day had identical amounts?