I’m working on a project where I need to write a query to get data for the sales manager where s/he wants to talk to a customer who had spent the most in the previous month. I’m having trouble figuring out how to just pull out data from the previous month based on the current month and just show the customer who spent most. I’m assuming I need to use MAX and LIMIT to find that answer. I’m expecting to get a result in this (Smith spent most in the previous month(Feb in this case as this month is March)
02 | "Smith S." | 123-123-1234 | $123.00
Sample tables are here:
order_lines order_id | order_line | customer_id | order_date | item_id | quantity_order 1 1 78 2021-02-02 23 3 items item_id | item_description | unit_price 23 baseball 29.99 customers customer_id | full_name | phone_number 78 Smith S. 123-123-1234
Here is my query so far… Any advice is very appreciated!
SELECT STRFTIME('%m', order_date) AS 'Month', customers.full_name AS 'Customers', customers.phone_number AS 'Phone', SUM(order_lines.quantity_ordered * items.unit_price) total_revenue FROM order_lines LEFT JOIN customers ON order_lines.customer_id = customers.customer_id LEFT JOIN items ON order_lines.item_id = items.item_id WHERE STRFTIME('%m', order_date) = (DATEPART(month, DATEADD(m, getdate()) - 1) AND DATEPART(year, order_date) = DATEPART(year, DATEADD(m, -1, getdate())) GROUP BY 1,2 ORDER BY 4 DESC LIMIT 1;