Write a query to display, bottom 2 Cities based on Quantity sold in the last two weeks. Kindly use deleted = 0 for all tables as default condition
data mining
Description
Ninjacart
– Analytics – SQL Assignment
Please find below the structure of
tables which will be used in the assignment below
Sale
order: Saleorder
Details:
|
Field |
Type |
|
id |
int(10) unsigned |
|
CustomerId |
int(10) unsigned |
|
CityId |
int(10) unsigned |
|
FacilityId |
int(10) unsigned |
|
DeliveryDate |
Date |
|
Deleted |
tinyint(1) |
|
Field |
Type |
|
id |
int(10) unsigned |
|
SaleOrderId |
int(10) unsigned |
|
SkuId |
int(10) unsigned |
|
SalePrice |
double |
|
PurchasePrice |
double |
|
SkuQuantity |
double |
|
WeightId |
int(10) unsigned |
|
ReturnQuantity |
double |
|
Deleted |
tinyint(1) |
Customer:
|
Field |
Type |
|
Id |
int(10) unsigned |
|
Name |
varchar(255) |
|
CityId |
int(10) unsigned |
|
RelationshipManager |
int(10) unsigned |
|
LocalityId |
int(10) unsigned |
|
Deleted |
tinyint(1) |
City: SKU:
|
Field |
Type |
|
Id |
int(10) unsigned |
|
Name |
varchar(255) |
|
Field |
Type |
|
Id |
int(10) unsigned |
|
Name |
varchar(255) |
Questions:
1) Write
a query to display, bottom 2 Cities based on Quantity sold in the last two
weeks. Kindly use deleted = 0 for all tables as default condition
2) Query
to list down SKU wise order count in the last 7 days (Kindly use deleted = 0
for all tables as default condition)
3) Query
to list down customer wise, number of full return instances. Kindly use deleted
= 0 for all tables as default condition)
4) Query
to list down City wise, facility wise average order values greater than 200.
Kindly use deleted = 0 for all tables as default condition)
5) Query
to list down Facility wise revenue
for the past three weeks in the below mention format. Kindly use deleted = 0
for all tables as default condition).
|
FACILITY_ID |
CURRENT WEEK |
LAST WEEK |
|
|
(Revenue) |
|
|
|
|
|
