You are tasked with optimizing a query in Snowflake that extracts information from a table called sales_data. The sales_data table contains information about sales transactions, including columns like product_id, quantity_sold, price, and transaction_date.
Your goal? Retrieving the top 10 products with the highest total revenue, where the total revenue is calculated as the sum of the product of quantity_sold and price for each transaction.
Hint: In a SELECT statement, the QUALIFY clause filters the results of window functions.
To create the table
-- Create sales_data table
CREATE TABLE sales_data (
product_id INT,
quantity_sold INT,
price DECIMAL(10,2),
transaction_date DATE
);
-- Insert sample values
INSERT INTO sales_data (product_id, quantity_sold, price, transaction_date)
VALUES
(1, 10, 15.99, '2024-02-01'),
(1, 8, 15.99, '2024-02-05'),
(2, 15, 22.50, '2024-02-02'),
(2, 20, 22.50, '2024-02-07'),
(3, 12, 10.75, '2024-02-03'),
(3, 18, 10.75, '2024-02-08'),
(4, 5, 30.25, '2024-02-04'),
(4, 10, 30.25, '2024-02-09'),
(5, 25, 18.50, '2024-02-06'),
(5, 30, 18.50, '2024-02-10');
Be sure to share your coded answers in the comments!
Thank you so much for the great advice Can Höbek! Your contribution was very helpful, how would I do without you.
Remember if you want to participate:
- Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
- Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
- Post the URL in the comments of the challenge.
Leave a Reply
You must be logged in to post a comment.