lOMoARcPSD|58664495
Business Case 1: Target SQL
Samarth Lavhate
--[Link] the dataset and do usual exploratory analysis steps like
checking the structure & characteristics of the dataset:
--1. Data type of all columns in the "customers" table.
Query:
select column_name, data_type from
`Target_Analysis.INFORMATION_SCHEMA.COLUMNS`
Where
table_name = 'customers';
Result:
Inference:
Data type of columns, with column names can be obtained by using
Information schema.
This helps in understanding the structure of the table and the kind
of attributes the table stores.
--2. Get the time range between which the orders were placed.
Query:
SELECT
min(order_purchase_timestamp) as minvalue,
max(order_purchase_timestamp) as maxvalue
from `scaler-dsml-sql-393406.Target_Analysis.orders`
Downloaded by Samarth Lavhate (samarthlavhate00@[Link])
lOMoARcPSD|58664495
Result:
Inference:
Time range can be obtained from purchase time details of the orders.
Hence the min() and max() functions can give the duration of the
orders placed.
--[Link] the Cities & States of customers who ordered during the
given period.
Query:
select count(distinct c.customer_state),
count(distinct customer_city)
from
`scaler-dsml-sql-393406.Target_Analysis.customers` c
inner join
`scaler-dsml-sql-393406.Target_Analysis.orders` o
on c.customer_id = o.customer_id
Result:
Inference:
Fetching the customers only orders table would give the details of
customers who placed orders during the given period.
--II. In-depth Exploration:
--1. Is there a growing trend in the no. of orders placed over the
past years?
Query:
SELECT
extract(year from order_purchase_timestamp) as year,
extract(month from order_purchase_timestamp) as month,
lOMoARcPSD|58664495
count(order_id) as no_of_orders
FROM `scaler-dsml-sql-393406.Target_Analysis.orders`
group by 1,2
order by 1,2
Result:
Inference:
No: of orders can be calculated by count() function grouping by year
and month.
As we see the no: of orders do not follow any growing trend in the
initial months, it reaches peak during nov 2017 and then lowers
during dec because of holiday, then follows a constant pattern till
Aug 2018 and falls again.
In all, there is no particular pattern that the no. of orders placed
over the years.
--2. Can we see some kind of monthly seasonality in terms of the no.
of orders being placed?
Query:
SELECT
extract(month from order_purchase_timestamp) as year,
count(distinct order_id) as no_of_orders FROM `scaler-dsml-sql-
393406.Target_Analysis.orders`
group by 1
order by 1
Downloaded by Samarth Lavhate (samarthlavhate00@[Link])
lOMoARcPSD|58664495
Result:
Inference:
By grouping on month, we can count the number of orders using
count() aggregate function.
When we sort them in an order, a comparison analysis can be done.
We notice the presence of seasonality in only specific months where
there is no holiday.
--3. During what time of the day, do the Brazilian customers mostly
place their orders? (Dawn, Morning, Afternoon or Night)
0-6 hrs : Dawn
7-12 hrs : Mornings
13-18 hrs : Afternoon
19-23 hrs : Night
Query:
SELECT
case
when extract(hour from order_purchase_timestamp) between 0 and 6
then 'Dawn'
when extract(hour from order_purchase_timestamp) between 7 and 12
then 'Mornings'
when extract(hour from order_purchase_timestamp) between 13 and 18
then 'Afternoon'
when extract(hour from order_purchase_timestamp) between 19 and 23
then 'Night'
end as time_of_the_day,
count(order_id) as no_of_orders
FROM `scaler-dsml-sql-393406.Target_Analysis.orders`
Downloaded by Samarth Lavhate (samarthlavhate00@[Link])
lOMoARcPSD|58664495
group by time_of_the_day
order by no_of_orders
Result:
Inference:
From the timestamp of orders, we can get the time of placing an
order.
Using case when expression, we can check the condition if the time
or order is during dawn or mornings or night or afternoon.
No: of orders can be counted using count() aggregate function by
grouping according to the time lap.
Further strategical analysis can be done and company can pull extra
add-ons during peak hours to multiply the sales.
--III. Evolution of E-commerce orders in the Brazil region
--1. Get the month on month no. of orders placed in each state.
Query:
SELECT
C.customer_state,
extract(month from O.order_purchase_timestamp) as monthly_orders,
format_datetime('%b', O.order_purchase_timestamp) as month,
count(O.order_id) as no_of_orders
FROM `scaler-dsml-sql-393406.Target_Analysis.orders` O
inner join
`scaler-dsml-sql-393406.Target_Analysis.customers` C
on C.customer_id = O.customer_id
group by 1,2,3
order by 1,2
Result:
Downloaded by Samarth Lavhate (samarthlavhate00@[Link])
lOMoARcPSD|58664495
Inference:
This analysis helps to get insights into customer purchase trends on
state by state basis.
The state that has highest no. of orders in an given months or least
no. of orders can be found out.
--2. How are the customers distributed across all the states?
Query:
SELECT customer_state,
count(customer_unique_id) as no_of_customers
FROM `scaler-dsml-sql-393406.Target_Analysis.customers`
group by 1
order by 1
Result:
Downloaded by Samarth Lavhate (samarthlavhate00@[Link])
lOMoARcPSD|58664495
Inference:
Grouping the states and counting the no: of customers would help us
know from which state the orders are being placed more.
Further orders placed can be calculated to improve the product
patterns accordingly.
--IV. Impact on Economy: Analyze the money movement by e-commerce by
looking at order prices, freight and others.
--1. Get the % increase in the cost of orders from year 2017 to 2018
(include months between Jan to Aug only).
--You can use the "payment_value" column in the payments table to
get the cost of orders.
Query:
with cte1 as(
select
sum(p.payment_value) as orderval2017,
extract(year from o.order_purchase_timestamp) as year,
format_datetime('%b', o.order_purchase_timestamp) as month,
from `scaler-dsml-sql-393406.Target_Analysis.orders` o
join
`scaler-dsml-sql-393406.Target_Analysis.payments` p
on o.order_id = p.order_id
where extract(year from o.order_purchase_timestamp) = 2017
and extract(month from order_purchase_timestamp) between 1 and 8
group by 2,3
order by 2,3),
Downloaded by Samarth Lavhate (samarthlavhate00@[Link])
lOMoARcPSD|58664495
cte2 as (select
sum(p.payment_value) as orderval2018,
extract(year from o.order_purchase_timestamp) as year,
format_datetime('%b', o.order_purchase_timestamp) as month,
extract(month from o.order_purchase_timestamp) as monthnum
from `scaler-dsml-sql-393406.Target_Analysis.orders` o
join
`scaler-dsml-sql-393406.Target_Analysis.payments` p
on o.order_id = p.order_id
where extract(year from o.order_purchase_timestamp) = 2018
and extract(month from order_purchase_timestamp) between 1 and 8
group by 2,3,4
order by 2,3)
select
[Link],
((b.orderval2018-a.orderval2017)/a.orderval2017)*100 as
percent_change
from cte1 a inner join cte2 b
on [Link] = [Link]
order by [Link]
Result:
Inference:
Cte, common table expression is a temporary relational table which
can be used later in a SQL statement.
The table is called temp because it exits only during the scope of
the sql statement written after the CTE.
As attributes of 2 years are to be observed, 2 cte tables are used
to filter the data.
Further % increase formula is applied to observe the change in the
cost of orders.
Downloaded by Samarth Lavhate (samarthlavhate00@[Link])
lOMoARcPSD|58664495
January shows highest percentage change followed by Feb and April.
--2. Calculate the Total & Average value of order price for each
state.
Query:
SELECT
c.customer_state,
round(sum([Link])) as sum_price,
round(avg([Link])) as avg_price,
FROM `scaler-dsml-sql-393406.Target_Analysis.order_items` oi
inner join
`scaler-dsml-sql-393406.Target_Analysis.orders` o
on o.order_id = oi.order_id
inner join
`scaler-dsml-sql-393406.Target_Analysis.customers` c
on o.customer_id = c.customer_id
group by c.customer_state
order by 1
Result:
Inference:
The avg and sum of order price can be calculated by joining the
orders and customers table
--3. Calculate the Total & Average value of order freight for each
state.
Downloaded by Samarth Lavhate (samarthlavhate00@[Link])
lOMoARcPSD|58664495
Query:
SELECT
c.customer_state,
round(sum(oi.freight_value)) as sum_freight,
round(avg(oi.freight_value)) as avg_freight
FROM `scaler-dsml-sql-393406.Target_Analysis.order_items` oi
inner join
`scaler-dsml-sql-393406.Target_Analysis.orders` o
on o.order_id = oi.order_id
inner join
`scaler-dsml-sql-393406.Target_Analysis.customers` c
on o.customer_id = c.customer_id
group by c.customer_state
order by 1
Result:
Inference:
The avg and sum of freight value can be calculated by joining the
orders and customers table
--V. Analysis based on sales, freight and delivery time.
--1. Find the no. of days taken to deliver each order from the
order’s purchase date as delivery time.
--Also, calculate the difference (in days) between the estimated &
actual delivery date of an order.
--Do this in a single query.
Downloaded by Samarth Lavhate (samarthlavhate00@[Link])
lOMoARcPSD|58664495
Query:
SELECT
order_id,
date_diff(order_delivered_customer_date, order_purchase_timestamp,
day) as time_to_deliver,
date_diff(order_estimated_delivery_date,
order_delivered_customer_date, day) as diff_estimated_delivery
FROM `scaler-dsml-sql-393406.Target_Analysis.orders`
where order_delivered_customer_date is not null
and order_purchase_timestamp is not null
and order_estimated_delivery_date is not null
and order_delivered_customer_date is not null
order by time_to_deliver desc
Result:
Inference:
With the difference in the delivery time and estimated time,
potential measures can be taken to enhance the fleet dispatching,
route optimization and all activities that reduce chances of delays.
--2. Find out the top 5 states with the highest & lowest average
freight value.
Query:
with del_time as
(SELECT g.geolocation_state,
avg(date_diff
Downloaded by Samarth Lavhate (samarthlavhate00@[Link])
lOMoARcPSD|58664495
(o.order_delivered_customer_date, o.order_purchase_timestamp, day))
as avg_delivery_time,
case when
dense_rank() over(order by
avg(date_diff
(o.order_delivered_customer_date, o.order_purchase_timestamp, day))
desc) <=5 then 'highest_delivery_time'
when
dense_rank() over(order by
avg(date_diff(o.order_delivered_customer_date,
o.order_purchase_timestamp, day)))<=5 then 'lowest_delivery_time'
end as delivery_time_rank
FROM `scaler-dsml-sql-393406.Target_Analysis.orders` o
inner join `scaler-dsml-sql-393406.Target_Analysis.customers` c
on o.customer_id = c.customer_id
inner join `scaler-dsml-sql-393406.Target_Analysis.geolocation` g
on g.geolocation_zip_code_prefix = c.customer_zip_code_prefix
group by 1)
select geolocation_state,
avg_delivery_time,
delivery_time_rank
from del_time
where delivery_time_rank is not null
order by del_time.avg_delivery_time
Result:
Inference:
The result can be used to evaluate asset use, performance, baseline
deviations and other focal points.
--3. Find out the top 5 states with the highest & lowest average
delivery time.
Downloaded by Samarth Lavhate (samarthlavhate00@[Link])
lOMoARcPSD|58664495
Query:
with del_time as
(SELECT g.geolocation_state,
avg(date_diff
(o.order_delivered_customer_date, o.order_purchase_timestamp, day))
as avg_delivery_time,
case when
dense_rank() over(order by
avg(date_diff
(o.order_delivered_customer_date, o.order_purchase_timestamp, day))
desc) <=5 then 'highest_delivery_time'
when
dense_rank() over(order by
avg(date_diff(o.order_delivered_customer_date,
o.order_purchase_timestamp, day)))<=5 then 'lowest_delivery_time'
end as delivery_time_rank
FROM `scaler-dsml-sql-393406.Target_Analysis.orders` o
inner join `scaler-dsml-sql-393406.Target_Analysis.customers` c
on o.customer_id = c.customer_id
inner join `scaler-dsml-sql-393406.Target_Analysis.geolocation` g
on g.geolocation_zip_code_prefix = c.customer_zip_code_prefix
group by 1)
select geolocation_state,
avg_delivery_time,
delivery_time_rank
from del_time
where delivery_time_rank is not null
order by del_time.avg_delivery_time
Result
Downloaded by Samarth Lavhate (samarthlavhate00@[Link])
lOMoARcPSD|58664495
Inference:
Identifying the state through geolocation_state is preferred as
customer_state always might not a valid value.
Cte is used as various tables are joined and subquering would affect
the code readability.
--4. Find out the top 5 states where the order delivery is really
fast as compared to the estimated date of delivery.
Query:
select c.customer_state,
avg(datetime_diff(order_estimated_delivery_date, order_delivered_customer_date, day)) as
fast_deliveries
from `scaler-dsml-sql-393406.Target_Analysis.orders` o
inner join `scaler-dsml-sql-393406.Target_Analysis.customers` c
on o.customer_id = c.customer_id
where order_delivered_customer_date is not null
group by 1
order by 2 desc
limit 5
Result:
Inference:
The difference between estimated delivery and actual delivery would
help to find the rate at which the orders are delivered.
Result:
Downloaded by Samarth Lavhate (samarthlavhate00@[Link])
lOMoARcPSD|58664495
Inference:
Avg delivery time and avg estimated time are calculated by filtering
according to the state.
--VI. Analysis based on the payments
--1. Find the month on month no. of orders placed using different
payment types.
Query:
SELECT
p.payment_type,
count(o.order_id) as no_of_orders,
extract(month from o.order_purchase_timestamp) as month,
extract(year from o.order_purchase_timestamp) as year,
format_datetime('%b', o.order_purchase_timestamp) as month_name
FROM `scaler-dsml-sql-393406.Target_Analysis.payments` p
inner join
`scaler-dsml-sql-393406.Target_Analysis.orders` o
on p.order_id = o.order_id
group by 1,3,4,5
order by 1,3,4
Result:
Inference:
To understand the trends in payment types, analysis on month-over-
month count of orders for different payment types is done.
Using extract(), month and year are extracted and group statewise.
Downloaded by Samarth Lavhate (samarthlavhate00@[Link])
lOMoARcPSD|58664495
--2. Find the no. of orders placed on the basis of the payment
installments that have been paid.
Query:
SELECT
p.payment_installments,
count(o.order_id) as order_count,
FROM `scaler-dsml-sql-393406.Target_Analysis.payments` p
inner join
`scaler-dsml-sql-393406.Target_Analysis.orders` o
on p.order_id = o.order_id
where p.payment_installments!=0
group by 1
order by 1
Result:
Inference:
Status of the payments instalments can be found by joining orders and payments table.
But to find the customers who have paid an instalment, filtering and removing the non-paid
customers can be obtained by passing condition payment_installments!=0 in where clause.
Downloaded by Samarth Lavhate (samarthlavhate00@[Link])