Recap - Total Gain Shop

Part of the Fundamentals section of Coddy's SQL journey. Lesson 40 of 72.

Challenge

Easy

Available tables and columns:
shop: price, quantity, category, list_date

Your task is to calculate the total revenue for each category of items in a shop between January 1, 2015, and March 18, 2015. Due to a systematic error in the data entry system, all price values in the database are lower than they should be by a fixed amount.

To correct these prices:

First, calculate the average price across all items within the specified date range (2015-01-01 (January 1, 2015), to 2015-03-18 March 18, 2015)
Add this average price to each item's original price to get the correct price value
For each category, calculate the total revenue by multiplying the corrected price by the quantity and summing these values
Present the results as (category, total_revenue) pairs, sorted by total revenue in descending order

Try it yourself

-- Step 2: group the rebuilt rows and total them up, largest first
SELECT ____
FROM (
    -- Step 1: rebuild each row so the corrected value replaces the original one
    SELECT ____ AS price, quantity, category, list_date
    FROM shop
    WHERE list_date BETWEEN '2015-01-01' AND '2015-03-18'
)
GROUP BY ____
ORDER BY ____ DESC

All lessons in Fundamentals

Practice on your own: SQL playground