How can I grab top selling product for each month using sql?

Question

Given a table with monthly transactions (customer id, month, payment) and a table with customer info (type 2 dimension) (id, cust_id, plan type, num users, start_date, end_date):

What is the top grossing plan each month (month, $, plan)?

My answer below seems like it would only return the top products plan by amount rather than per month.

SELECT 
    Sales.month as SalesMonth, 
    SUM(Sales.payment) AS MonthlySales, 
    CustomerInfo.plan_type AS PlanType 
FROM Sales 
INNER JOIN CustomerInfo ON Sales.customer_id=CustomerInfo.cust_id
GROUP BY SalesMonth, MonthlySaleS, PlanType 
ORDER BY MonthlySales, PlanType
ORDER BY MonthlySales DESC 
LIMIT 1

I am stumped on the next two.

2) Given the above tables how many customers are brought on every month (month, plan, # new customers)?

3) Given the above tables, how many people switch plans per month (month, from plan to plan, # customers)?

Answer 1

You could proceed as follows:

• first use an aggregate query to compute the monthly sales per plan
• then rank records by descending monthly sales within months partitions
• finally, filter on the top record in each month

Query:

SELECT SalesMonth, PlanType, MonthlySales
FROM (
    SELECT 
        x.*, 
        ROW_NUMBER() OVER(PARTITION BY as SalesMonth ORDER BY MonthlySales desc) rn
    FROM (
        SELECT 
            s.month as SalesMonth, 
            c.plan_type AS PlanType, 
            SUM(s.payment) AS MonthlySales
        FROM sales s
        INNER JOIN CustomerInfo s ON s.customer_id = c.cust_id
        GROUP BY s.month, c.plan_type
    ) x
) y
WHERE rn = 1