Customers Active in Both Years

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Customers Active in Both Years. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

Amazon Retail wants to identify customers who were consistently active across 2019 and 2020. Write a PostgreSQL query that returns customers with more than three transactions in each of those two calendar years.

Requirements

Join customers to transactions using customer_id.
Count each customer's transactions separately for 2019 and 2020.
Return only customers whose count is greater than 3 in both years.
Include the customer ID, customer name, and both yearly transaction counts. Order the output by customer_id.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique Amazon customer identifier
customer_name	VARCHAR(100)	Customer display name
marketplace	VARCHAR(30)	Amazon marketplace associated with the customer

transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique transaction identifier
customer_id	INTEGER	Customer who made the transaction
transaction_date	DATE	Date of the transaction
amount	NUMERIC(10,2)	Transaction amount in USD

Tablescustomerstransactions

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results