Drill 3 of 8 in Fintech questions
0

Interview Question Bank · Window AVG · compare a row with its own group

3. Purchases over 3x the customer's average

MediumFintechQuestion #102

Concept: Window AVG · compare a row with its own groupshow

To compare each row with its own group's average, AVG(x) OVER (PARTITION BY group) puts that average on every row without collapsing them. Window functions can't be used in WHERE, because WHERE runs before they are computed, so calculate the average in a CTE or subquery and filter in the outer query.

Example (different data):

SELECT store_id, sale_id, total,
  ROUND(AVG(total) OVER (PARTITION BY store_id), 2) AS store_avg
FROM sales;

Your task

Tallyfin's fraud model flags card purchases that are far out of line for that particular customer: a $1,250 laptop is routine for some customers and alarming for others.

Return customer_id, purchase_id, amount and customer_avg (the customer's average amount over all their purchases, including this one, ROUND(..., 2)) for every purchase in fin_purchases that is more than 3 times its customer's average. Sort by customer_id, purchase_id.

Starting Postgres in your browser…

Checking your access…