← Point72 Interview Insights

Point72·Software Engineer·Online Assessment (OA)·Intermediate

IntermediatePrefer not to say
Jun 2026Remote

Summary

SQL-heavy online assessment for a Data Engineer role at Point72, one problem but it was a beast. The question looked like a straightforward aggregation task until you got into the formatting requirements and started hitting weird PostgreSQL edge cases.

Questions Asked (1)

Q1

Given a cryptocurrency exchange transaction table, write a PostgreSQL query to detect potential fraud by finding sender-recipient pairs with 2 or more transactions, then aggregate results per sender showing: the time period of activity, and a formatted string of transaction amounts sorted descending (top 3 shown explicitly, the rest collapsed with a count suffix). Order final output by total amount descending.

Algorithms & Data StructuresData ModelingTechnical Trade-offs
Author's notes

This one ate up way more time than it should have.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into three logical stages: first identify sender-recipient pairs with at least two transactions using GROUP BY and HAVING, then aggregate per sender to compute the activity time period and a formatted string of transaction amounts, and finally order the results by total amount descending. Use CTEs to keep the query readable and modular, and leverage window functions or string aggregation with ordering to build the formatted amount string.

Pro tip: Mention that you would validate the query against edge cases like ties in transaction amounts, null values, and timezone handling, and discuss whether to use STRING_AGG with ORDER BY or a window function for the top-3-plus-count formatting—showing awareness of PostgreSQL-specific features and performance implications.

1. Identify frequent sender-recipient pairs

Use a CTE to group transactions by sender and recipient, applying HAVING COUNT(*) >= 2 to filter pairs with two or more transactions. This isolates potential fraud patterns.

2. Aggregate per sender

From the filtered pairs, group by sender to compute the overall time period (MIN and MAX transaction timestamps) and the total transaction amount for ordering.

3. Format transaction amounts string

For each sender, collect all transaction amounts, sort them descending, take the top 3 explicitly, and append a count of remaining amounts (e.g., '100, 90, 80, +5 more'). Use STRING_AGG with ORDER BY or a subquery with window functions.

4. Order and present final output

Order the final result set by total amount descending, and select the sender, time period, and formatted amount string. Ensure the query is readable and efficient.

Key Points to Mention

  • Use of CTEs (WITH clauses) to break down the query into logical steps for readability and maintainability.
  • HAVING COUNT(*) >= 2 to filter sender-recipient pairs with multiple transactions.
  • Aggregation functions: MIN and MAX for time period, SUM for total amount ordering.
  • String aggregation with ordering: STRING_AGG(amount::text, ', ' ORDER BY amount DESC) or similar, and handling the top-3-plus-count logic.
  • Window functions like ROW_NUMBER() to rank amounts per sender for selecting top 3 and counting the rest.
  • Performance considerations: indexing on sender, recipient, and transaction time, and avoiding unnecessary sorting or full table scans.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.