Find Top Customer by Year
HardPremium
You are given the following tables:
orders table:
departments table:
customers table:
For each of the last 5 years, identify the customer who placed the most orders, i.e. you should have a list of 5 top customers. Your output should have the following columns: year, customer_id, first_name, last_name, total_orders.
Example output:
WITH orders_per_year AS (
SELECT
c.customer_id,
c.first_name,
c.last_name,
strftime('%Y', o.order_date) AS year,
COUNT(o.order_id) AS total_orders,
rank() OVER (PARTITION BY strftime('%Y', o.order_date) ORDER BY COUNT(o.order_id) DESC) AS order_rankings
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE strftime('%Y', o.order_date) IN ('2019', '2020', '2021', '2022', '2023')
GROUP BY year, c.customer_id, c.first_name, c.last_name
)
SELECT
year,
customer_id,
first_name,
last_name,
total_orders
FROM orders_per_year
WHERE order_rankings = 1
ORDER BY year ASC;
Required output in the solution not the one requested from the question. only customer_id, first_name, last_name and years were required. Please this needs to be very clear.
Otherwise my answer is
with total_order_year as ( SELECT o.customer_id, c.first_name, c.last_name, EXTRACT(YEAR FROM o.order_date) AS order_year, COUNT(o.order_id) AS total_orders FROM orders o LEFT JOIN customers c ON c.customer_id = o.customer_id GROUP BY o.customer_id, c.first_name, c.last_name, EXTRACT(YEAR FROM o.order_date) ORDER BY o.customer_id, order_year ), ranked_total_orders as (select customer_id, first_name, last_name, order_year, total_orders, rank () over (partition by order_year order by total_orders DESC) as ranking_top_orders from total_order_year) select customer_id, first_name, last_name, order_year as years from ranked_total_orders where ranking_top_orders = 1 order by order_year ASC;