Skip to content

Latest commit

 

History

History
48 lines (29 loc) · 2.05 KB

Answers.md

File metadata and controls

48 lines (29 loc) · 2.05 KB

SQL_Assesment

Question #1 Generate a query to get the sum of the clicks of the marketing data

Select SUM(clicks) AS Sum_Of_Clicks FROM marketing_data;

Question #2 Generate a query to gather the sum of revenue by store_location from the store_revenue table

Select SUM(revenue) AS Sum_Of_Revenue, store_location FROM store_revenue GROUP BY store_location;

Question #3 Merge these two datasets so we can see impressions, clicks, and revenue together by date and geo. Please ensure all records from each table are accounted for.

Select store_revenue.date, geo, impressions, clicks, revenue FROM store_revenue LEFT JOIN marketing_data ON store_revenue.date = marketing_data.date AND right(store_revenue.store_location, 2) = marketing_data.geo UNION Select store_revenue.date, geo, impressions, clicks, revenue FROM store_revenue RIGHT JOIN marketing_data ON store_revenue.date = marketing_data.date AND right(store_revenue.store_location, 2) = marketing_data.geo;

Question #4 In your opinion, what is the most efficient store and why?

Brand 2 in California had the highest revenue per click and revenue per impression which is why it is the most efficient store.

Select store_revenue.date, brand_id,store_revenue.store_location AS geo, (revenue/impressions) AS Revenue_Per_Impression, (revenue/clicks) AS Revenue_Per_Click FROM store_revenue LEFT JOIN marketing_data ON store_revenue.date = marketing_data.date AND right(store_revenue.store_location, 2) = marketing_data.geo UNION Select store_revenue.date, brand_id, store_revenue.store_location AS geo, (revenue/impressions) AS Revenue_Per_Impression, (revenue/clicks) AS Revenue_Per_Click FROM store_revenue RIGHT JOIN marketing_data ON store_revenue.date = marketing_data.date AND right(store_revenue.store_location, 2) = marketing_data.geo;

Question #5 (Challenge) Generate a query to rank in order the top 10 revenue producing states

Select right(store_revenue.store_location, 2) AS State, SUM(revenue) AS Total_Revenue FROM store_revenue GROUP BY right(store_revenue.store_location, 2) ORDER BY SUM(revenue) desc LIMIT 10;