Recap - Total Gain Shop
Part of the Fundamentals section of Coddy's SQL journey — lesson 40 of 72.
Challenge
EasyAvailable tables and columns:
<strong>shop</strong>:<strong>price</strong>,<strong>quantity</strong>,<strong>category</strong>,<strong>list_date</strong>
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), to2015-03-18March 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
All lessons in Fundamentals
4More Keywords
The IN keywordThe BETWEEN keywordThe LIKE keywordThe AS keywordRecap - Cellphone Models
2Conditions
Conditions BasicsThe AND keywordThe OR keywordThe NOT keywordMultiple Conditions CombinedParenthesisBooleans5Arithmetic Operations
Mathematical OperatorsMathematical ColumnsThe Modulo OperationThe ROUND() Function3Specific Return Format
Null valuesSort Results Part 1Sort Results Part 2Recap - Cyber Security FirmLimit number of recordsRecap - Vehicle Factory6Intro Challenges
Recap - Parliamentary ElectionRecap - Police Criminal ArrestRecap - Bar Beverage ContainerRecap - Engineer new columns