T - SQL Real Time Scenario 4

T - SQL Real Time Scenario 4:

In this post, I am going to share some analytical SQL Queries which are generally used for analysis of data and most of them are asked in the SQL interviews:

We have two tables: Products and Transactions

Products table contains three columns:

ProductID	ProductSubCategory	ProductCategory
P1023	Storage & Organization	Office Supplies
P1024	Appliances	Office Supplies
P1025	Binders and Binder Accessories	Office Supplies
P1026	Telephones and Communication	Technology
P1027	Office Furnishings	Furniture

Transactions Table contains these columns:

 

You can download this sample data from this link: Sample Data for Data Analysis

For Create table and insert scripts also: SQL Create table and Insert scripts

So download these files for sample data and run script on you SQL Server to generate both tables with data.

You can also upload excel data in SQL through Import/Export data mechanism of SQL Server.

So basis on these two tables, we need to write SQL queries to generate result set for below scenarios:

Ø  Design a query to get all customer’s transactions with product details

Ø  Show 5 Highest selling Products with total counts for each year and Month (Build two queries for showing count and total sales amount)

Ø  Show 5 Lowest Selling Products with total counts for each year and Month (Build two queries for showing count and total sales amount)

Ø  Provide last transactions details done by each customer.

Ø  Show top 5 products which are having highest profit margin (after deduct shipping, discount and other charges)

Ø  Show Region wise top 5 products which are having high sales with highest profit margin with counts

Ø  Show 5 Region with products list and count of transactions, which are having lowest products list for sale.