Week 83 – Basic

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.

-- 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:

1. 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)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.