Homework answers / question archive / SQL CASE STUDY- ADVANCE Business Scenario: The database “Cellphones Information” contains details on cell phone sales or transactions

SQL CASE STUDY- ADVANCE Business Scenario: The database “Cellphones Information” contains details on cell phone sales or transactions

Statistics

Share With

SQL CASE STUDY- ADVANCE
Business Scenario: The database “Cellphones Information” contains details on cell phone sales or transactions.
Detailes stored are: Dim_manufacturer, Dim_model, Dim_customer, Dim_Location and Fact_Transactions.
The first four store entries for the respective elements and Fact_Transactions stores all the information about sales of specific cellphones.
Data Availability: Assume that you do not have access to the data. Hence, pls create a schema based on the representation below to work on the case study

Questions:
Write queries to find out the following:
1. List all the states in which we have customers who have bought cellphones from 2005 till today.
2. What state in the US is buying more 'Samsung' cell phones?
3. Show the number of transactions for each model per zip code per state.
4. Show the cheapest cellphone
5. Find out the average price for each model in the top5 manufacturers in
terms of sales quantity and order by average price.
6. List the names of the customers and the average amount spent in 2009,
where the average is higher than 500
7. List if there is any model that was in the top 5 in terms of quantity,
simultaneously in 2008, 2009 and 2010
8. Show the manufacturer with the 2nd top sales in the year of 2009 and the
manufacturer with the 2nd top sales in the year of 2010.
9. Show the manufacturers that sold cellphone in 2010 but didn’t in 2009.
10. Find top 100 customers and their average spend, average quantity by each year. Also find the percentage of change in their spend.