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)

 

 

 

 

 

 


Related Questions in data mining category