Sarwar Alam
1. Initial Exploration of data(Checking data types of columns,time period of the data,and
cities and states)
Data types:
Table: sellers
select column_name,data_type from TargetEcommerce.INFORMATION_SCHEMA.COLUMNS where
table_name = 'sellers';
Table:products
select column_name,data_type from TargetEcommerce.INFORMATION_SCHEMA.COLUMNS where
table_name = 'products';
Table:payments
select column_name,data_type from TargetEcommerce.INFORMATION_SCHEMA.COLUMNS where
table_name = 'payments';
Table:orders
select column_name,data_type from TargetEcommerce.INFORMATION_SCHEMA.COLUMNS where
table_name = 'orders';
Table:order_reviews
select column_name,data_type from TargetEcommerce.INFORMATION_SCHEMA.COLUMNS where
table_name = 'order_reviews';
Table:order_items
select column_name,data_type from TargetEcommerce.INFORMATION_SCHEMA.COLUMNS where
table_name = 'order_items';
Table:customers
select column_name,data_type from TargetEcommerce.INFORMATION_SCHEMA.COLUMNS where
table_name = 'customers';
Time period for which the data is given:
We need to find when the first purchase and the last purchase in the given data took
[Link] would be the time period of the whole data.
select
min(order_purchase_timestamp) as start_date,
max(order_purchase_timestamp) as end_date
from `[Link]`
Time period: From 2016-09-04 21:15:19 UTC to 2018-10-17 17:30:18 UTC
Cities and States covered in the dataset:
States:
select
distinct *
from (select
customer_state as state
from `[Link]`
UNION ALL
select
seller_state as state
from `[Link]`)
Cities:
select
distinct *
from (select
customer_city as city
from `[Link]`
UNION ALL
select
seller_city as city
from `[Link]`)
[Link]-depth Exploration:
1. Trend on e-commerce in Brazil:
[Link] find trend ,we can have the total sales for each month( from year 2016 to
2018):
Total purchase has been sorted in descending order.
with price_month AS (select
(CASE
WHEN extract(MONTH FROM o.order_purchase_timestamp)=1
THEN 'Jan'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=2
THEN 'Feb'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=3
THEN 'March'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=4
THEN 'April'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=5
THEN 'May'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=6
THEN 'June'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=7
THEN 'July'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=8
THEN 'Aug'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=9
THEN 'Sep'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=10
THEN 'Oct'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=11
THEN 'Nov'
ELSE 'Dec'
END) month,
[Link]
from `[Link]` o left join `TargetEcommerce.order_items` oi
on o.order_id=oi.order_id),
total_purchases AS (select month,sum(price) as total_sales from price_month
group by month)
select * from total_purchases order by total_sales desc;
Sales in May is more than double of sales in Sep
Surely there is a trend of [Link] is a trend(upward)
from January to March but suddenly in April the purchases go
[Link] April to May upward trend but in June the purchase go
[Link] some time there is a upward trend but there is also
downward [Link] a random walk.
[Link] we will have a trend for each year(2017,2018) with respect to each month,the
year 2016 has been excluded as the dataset has data only from Sep,Oct,Nov,and Dec.
Year 2017:Total purchase has been sorted in descending order
with price_month AS (select
(CASE
WHEN extract(MONTH FROM o.order_purchase_timestamp)=1
THEN 'Jan'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=2
THEN 'Feb'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=3
THEN 'March'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=4
THEN 'April'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=5
THEN 'May'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=6
THEN 'June'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=7
THEN 'July'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=8
THEN 'Aug'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=9
THEN 'Sep'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=10
THEN 'Oct'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=11
THEN 'Nov'
ELSE 'Dec'
END) month,
EXTRACT(YEAR FROM o.order_purchase_timestamp) as year,
[Link]
from `[Link]` o left join `TargetEcommerce.order_items` oi
on o.order_id=oi.order_id),
total_purchases AS (select month,sum(price) as total_sales from price_month
where year=2017 group by month)
select * from total_purchases order by total_sales desc;
In the year 2017,it’s surprising that from January to Dec there is an upward
trend.
Year 2018:Total purchase has been sorted in descending order
with price_month AS (select
(CASE
WHEN extract(MONTH FROM o.order_purchase_timestamp)=1
THEN 'Jan'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=2
THEN 'Feb'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=3
THEN 'March'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=4
THEN 'April'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=5
THEN 'May'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=6
THEN 'June'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=7
THEN 'July'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=8
THEN 'Aug'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=9
THEN 'Sep'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=10
THEN 'Oct'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=11
THEN 'Nov'
ELSE 'Dec'
END) month,
EXTRACT(YEAR FROM o.order_purchase_timestamp) as year,
[Link]
from `[Link]` o left join `TargetEcommerce.order_items` oi
on o.order_id=oi.order_id),
total_purchases AS (select month,sum(price) as total_sales from price_month
where year=2018 group by month)
select * from total_purchases order by total_sales desc;
It’s strange that there is no order in October 2018. September has the lowest
total [Link] other months the purchases are like random walks.
Week sales:2018
with price_week AS (select
EXTRACT(WEEK FROM o.order_purchase_timestamp) as week,
EXTRACT(YEAR FROM o.order_purchase_timestamp) as year,
[Link]
from `[Link]` o right join `TargetEcommerce.order_items` oi
on o.order_id=oi.order_id),
total_purchases AS (select week,sum(price) as total_sales from price_week where
year=2018 group by week)
select * from total_purchases order by total_sales desc;
Highest week sale is in week 18 and lowest week sale is in week 36.
Week sales:2017
with price_week AS (select
EXTRACT(WEEK FROM o.order_purchase_timestamp) as week,
EXTRACT(YEAR FROM o.order_purchase_timestamp) as year,
[Link]
from `[Link]` o right join `TargetEcommerce.order_items` oi
on o.order_id=oi.order_id),
total_purchases AS (select week,sum(price) as total_sales from price_week where
year=2017 group by week)
select * from total_purchases order by total_sales desc;
Highest week is in week 47 and lowest week sale is in week 3.
The year 2017 has a trend of purchases based on weeks.
[Link] time do Brazilian customers tend to buy (Dawn, Morning, Afternoon or Night)?
with hour_count AS (select oi.product_id,EXTRACT(HOUR FROM o.order_purchase_timestamp)
as hour from `[Link]` o join `TargetEcommerce.order_items` oi on
o.order_id=oi.order_id),
hour_named AS (select
product_id,
case
when hour between 4 and 6
THEN 'Dawn'
when hour>6 and hour<=12
then 'Morning'
when hour>12 and hour<=18
then 'afternoon'
else 'Night'
END as hour_label
from hour_count),
total_count as (select hour_label,count(product_id) as total_purchase from hour_named
group by hour_label)
select * from total_count order by total_purchase;
Brazilians prefer Afternoon as the ideal time to make a purchase ,Dawn is the least,
followed by morning.
[Link] of E-commerce orders in the Brazil region:
a. month on month orders by region, states:
-- to get the orders month by month for every city and states
-- we need to join tables customers,orders,and order_items
with city_orders AS (select
c.customer_city,
(CASE
WHEN extract(MONTH FROM o.order_purchase_timestamp)=1
THEN 'Jan'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=2
THEN 'Feb'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=3
THEN 'March'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=4
THEN 'April'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=5
THEN 'May'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=6
THEN 'June'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=7
THEN 'July'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=8
THEN 'Aug'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=9
THEN 'Sep'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=10
THEN 'Oct'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=11
THEN 'Nov'
ELSE 'Dec'
END) month,
oi.order_item_id
from `[Link]` c left join `[Link]` o
on o.customer_id=c.customer_id left join `TargetEcommerce.order_items` oi
on oi.order_id=o.order_id ),
total_orders_city AS (select customer_city as city,month,count(order_item_id) as
total_orders from city_orders group by customer_city,month )
select * from total_orders_city;
City wise month on month total number of orders placed by customers.
-- to get the orders month by month for every city and states
-- we need to join tables customers,orders,and order_items
with state_orders AS (select
c.customer_state,
(CASE
WHEN extract(MONTH FROM o.order_purchase_timestamp)=1
THEN 'Jan'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=2
THEN 'Feb'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=3
THEN 'March'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=4
THEN 'April'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=5
THEN 'May'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=6
THEN 'June'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=7
THEN 'July'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=8
THEN 'Aug'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=9
THEN 'Sep'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=10
THEN 'Oct'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=11
THEN 'Nov'
ELSE 'Dec'
END) month,
oi.order_item_id
from `[Link]` c left join `[Link]` o
on o.customer_id=c.customer_id left join `TargetEcommerce.order_items` oi
on oi.order_id=o.order_id ),
total_orders_state AS (select customer_state as state,month,count(order_item_id) as
total_orders from state_orders group by customer_state,month )
select * from total_orders_state;
State wise month on month total numbers placed by customers.
[Link] are customers distributed in Brazil
City-wise:
select customer_city,count(*) as number_customers from
`[Link]`group by customer_city order by count(*) desc;
City-wise number of [Link] Paulo has the highest number of customers.
State-wise:
select customer_state,count(*) as number_customers from
`[Link]`group by customer_state order by count(*) desc;
State of São Paulo is the state with the highest number of customers.
[Link] on Economy: Analyze the money movemented by e-commerce by looking at
order prices, freight and others.
[Link] % increase in cost of orders from 2017 to 2018 (include months between Jan to
Aug only)
Percentage change from 2017 to 2018 based on the freight values from month Jan to
Aug
with price_percentage_change AS (select
(CASE
WHEN extract(MONTH FROM o.order_purchase_timestamp)=1
THEN 'Jan'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=2
THEN 'Feb'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=3
THEN 'March'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=4
THEN 'April'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=5
THEN 'May'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=6
THEN 'June'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=7
THEN 'July'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=8
THEN 'Aug'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=9
THEN 'Sep'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=10
THEN 'Oct'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=11
THEN 'Nov'
ELSE 'Dec'
END) month,
oi.freight_value,
[Link],
EXTRACT(YEAR FROM o.order_purchase_timestamp) as year
from `[Link]` o left join `TargetEcommerce.order_items` oi
on o.order_id=oi.order_id),
freight as (select year,sum(freight_value) as total_freight_value from
price_percentage_change where month in
('Jan','Feb','March','April','May','June','July','Aug') and year in (2017,2018) group
by year)
select
100*(curr.total_freight_value-prev.total_freight_value)/prev.total_freight_value as
percent_change
from freight as curr
join freight as prev
on [Link]=2018 and [Link]=2017;
There is 152% increase of freight value from 2017 to 2018
Percentage change from 2017 to 2018 based on the price from month Jan to Aug
with price_percentage_change AS (select
(CASE
WHEN extract(MONTH FROM o.order_purchase_timestamp)=1
THEN 'Jan'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=2
THEN 'Feb'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=3
THEN 'March'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=4
THEN 'April'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=5
THEN 'May'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=6
THEN 'June'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=7
THEN 'July'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=8
THEN 'Aug'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=9
THEN 'Sep'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=10
THEN 'Oct'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=11
THEN 'Nov'
ELSE 'Dec'
END) month,
oi.freight_value,
[Link],
EXTRACT(YEAR FROM o.order_purchase_timestamp) as year
from `[Link]` o left join `TargetEcommerce.order_items` oi
on o.order_id=oi.order_id),
prices as (select year,sum(price) as total_price_value from price_percentage_change
where month in ('Jan','Feb','March','April','May','June','July','Aug') and year in
(2017,2018) group by year)
select 100*(curr.total_price_value-prev.total_price_value)/prev.total_price_value as
percent_change
from prices as curr
join prices as prev
on [Link]=2018 and [Link]=2017;
There is a 137% increase in price of the products from 2017 to 2018 based on the
months from Jan to Aug .
[Link] & Sum of price and freight value by customer state
with state_order as (select c.customer_state,
[Link],
oi.freight_value
from `[Link]` c left join `[Link]`o
on c.customer_id=o.customer_id
left join `TargetEcommerce.order_items` oi
on oi.order_id=o.order_id)
select customer_state,sum(price) as total_price,avg(price) as
average_price,sum(freight_value) as total_freight,
avg(freight_value) as average_freight_value from state_order group by customer_state
order by sum(price) desc,sum(freight_value) desc;
The state of Sao Paulo has the highest total price whereas the state of
Roraima has the lowest total price.
[Link] on sales, freight and delivery time
a & [Link] between purchasing, delivering and estimated delivery.
-- days between purchasing, delivering and estimated delivery
with delivery_days AS (select
-- delivery time between purchase and delivery date
DATE_DIFF(order_delivered_customer_date,order_purchase_timestamp,DAY) as
time_to_deliver,
-- difference between actual delivery date and estimated delivery date
DATE_DIFF(order_estimated_delivery_date,order_delivered_customer_date,DAY) as
diff_estimated_delivery
from `[Link]`)
select time_to_deliver AS days_between_purchase_and_deliver,
diff_estimated_delivery AS days_difference_between_actual_estimated_delivery
from delivery_days;
There are some values in the third column that are negative which indicates that the
actual delivery took x number of more days than the estimated delivery time.
[Link] data by state, take mean of freight_value, time_to_delivery,
diff_estimated_delivery
with price_days_freight AS (select
c.customer_state,
DATE_DIFF(o.order_delivered_customer_date,o.order_purchase_timestamp,DAY) as
time_to_deliver,
DATE_DIFF(o.order_estimated_delivery_date,o.order_delivered_customer_date,DAY) as
diff_estimated_delivery,
oi.freight_value
from `[Link]` c left join `[Link]` o
on c.customer_id=o.customer_id left join `TargetEcommerce.order_items` oi
on oi.order_id=o.order_id
select customer_state as state,avg(freight_value) as
average_freight_value,avg(time_to_deliver) as average_days_between_purchase_delivery,
avg(diff_estimated_delivery) as average_days_diff_between_actual_estimated_delivery
from price_days_freight group by customer_state;
The state of RN took on average 18 days to delivery a product to the customer
[Link] 5 states with highest/lowest average freight value - sort in desc/asc limit 5
with price_days_freight AS (select
c.customer_state,
DATE_DIFF(o.order_delivered_customer_date,o.order_purchase_timestamp,DAY) as
time_to_deliver,
DATE_DIFF(o.order_estimated_delivery_date,o.order_delivered_customer_date,DAY) as
diff_estimated_delivery,
oi.freight_value
from `[Link]` c left join `[Link]` o
on c.customer_id=o.customer_id left join `TargetEcommerce.order_items` oi
on oi.order_id=o.order_id
),
states_with_avg_freight as (select customer_state as state,avg(freight_value) as
average_freight_value,avg(time_to_deliver) as average_days_between_purchase_delivery,
avg(diff_estimated_delivery) as average_days_diff_between_actual_estimated_delivery
from price_days_freight group by customer_state)
-- top 5 states with heighest average freight value
select state,average_freight_value from states_with_avg_freight order by
average_freight_value desc limit 5;
Top 5 states with highest average freight value
with price_days_freight AS (select
c.customer_state,
DATE_DIFF(o.order_delivered_customer_date,o.order_purchase_timestamp,DAY) as
time_to_deliver,
DATE_DIFF(o.order_estimated_delivery_date,o.order_delivered_customer_date,DAY) as
diff_estimated_delivery,
oi.freight_value
from `[Link]` c left join `[Link]` o
on c.customer_id=o.customer_id left join `TargetEcommerce.order_items` oi
on oi.order_id=o.order_id
),
states_with_avg_freight as (select customer_state as state,avg(freight_value) as
average_freight_value,avg(time_to_deliver) as average_days_between_purchase_delivery,
avg(diff_estimated_delivery) as average_days_diff_between_actual_estimated_delivery
from price_days_freight group by customer_state)
-- top 5 states with heighest average freight value
select state,average_freight_value from states_with_avg_freight order by
average_freight_value asc limit 5;
Top 5 states with lowest average freight value
[Link] 5 states with highest/lowest average time to delivery
with price_days_freight AS (select
c.customer_state,
DATE_DIFF(o.order_delivered_customer_date,o.order_purchase_timestamp,DAY) as
time_to_deliver,
DATE_DIFF(o.order_estimated_delivery_date,o.order_delivered_customer_date,DAY) as
diff_estimated_delivery,
oi.freight_value
from `[Link]` c left join `[Link]` o
on c.customer_id=o.customer_id left join `TargetEcommerce.order_items` oi
on oi.order_id=o.order_id
),
states_with_avg_freight as (select customer_state as state,avg(freight_value) as
average_freight_value,avg(time_to_deliver) as average_days_between_purchase_delivery,
avg(diff_estimated_delivery) as average_days_diff_between_actual_estimated_delivery
from price_days_freight group by customer_state)
-- top 5 states with heighest average time to delivery
select state,average_days_between_purchase_delivery as average_time_to_delivery from
states_with_avg_freight order by average_days_between_purchase_delivery desc limit 5;
Top 5 states with highest average time to delivery.
with price_days_freight AS (select
c.customer_state,
DATE_DIFF(o.order_delivered_customer_date,o.order_purchase_timestamp,DAY) as
time_to_deliver,
DATE_DIFF(o.order_estimated_delivery_date,o.order_delivered_customer_date,DAY) as
diff_estimated_delivery,
oi.freight_value
from `[Link]` c left join `[Link]` o
on c.customer_id=o.customer_id left join `TargetEcommerce.order_items` oi
on oi.order_id=o.order_id
),
states_with_avg_freight as (select customer_state as state,avg(freight_value) as
average_freight_value,avg(time_to_deliver) as average_days_between_purchase_delivery,
avg(diff_estimated_delivery) as average_days_diff_between_actual_estimated_delivery
from price_days_freight group by customer_state)
-- top 5 states with heighest average time to delivery
select state,average_days_between_purchase_delivery as average_time_to_delivery from
states_with_avg_freight order by average_days_between_purchase_delivery asc limit 5;
Top 5 states with lowest average time to delivery.
[Link] 5 states where delivery is really fast/ not so fast compared to estimated date
with price_days_freight AS (select
c.customer_state,
DATE_DIFF(o.order_delivered_customer_date,o.order_purchase_timestamp,DAY) as
time_to_deliver,
DATE_DIFF(o.order_estimated_delivery_date,o.order_delivered_customer_date,DAY) as
diff_estimated_delivery,
oi.freight_value
from `[Link]` c left join `[Link]` o
on c.customer_id=o.customer_id left join `TargetEcommerce.order_items` oi
on oi.order_id=o.order_id
),
states_with_avg_freight as (select customer_state as state,avg(freight_value) as
average_freight_value,avg(time_to_deliver) as average_days_between_purchase_delivery,
avg(diff_estimated_delivery) as average_days_diff_between_actual_estimated_delivery
from price_days_freight group by customer_state)
-- top 5 states where delivery is really fast compared to estimated date
-- to get this we need to sort the
"average_days_diff_between_actual_estimated_delivery" in ascending order
select state,average_days_diff_between_actual_estimated_delivery from
states_with_avg_freight order by average_days_diff_between_actual_estimated_delivery
asc limit 5;
Top 5 states where delivery really fast compared to estimated [Link] example the
state AL is the state with fastest delivery.
with price_days_freight AS (select
c.customer_state,
DATE_DIFF(o.order_delivered_customer_date,o.order_purchase_timestamp,DAY) as
time_to_deliver,
DATE_DIFF(o.order_estimated_delivery_date,o.order_delivered_customer_date,DAY) as
diff_estimated_delivery,
oi.freight_value
from `[Link]` c left join `[Link]` o
on c.customer_id=o.customer_id left join `TargetEcommerce.order_items` oi
on oi.order_id=o.order_id
),
states_with_avg_freight as (select customer_state as state,avg(freight_value) as
average_freight_value,avg(time_to_deliver) as average_days_between_purchase_delivery,
avg(diff_estimated_delivery) as average_days_diff_between_actual_estimated_delivery
from price_days_freight group by customer_state)
-- top 5 states where delivery is really slow compared to estimated date
-- to get this we need to sort the
"average_days_diff_between_actual_estimated_delivery" in ascending order
select state,average_days_diff_between_actual_estimated_delivery from
states_with_avg_freight order by average_days_diff_between_actual_estimated_delivery
desc limit 5;
Top 5 states where delivery is really slow compared to estimated [Link] example the
state AC is the slowest state in terms of delivery.
[Link] type analysis:
[Link] over Month count of orders for different payment types
with month_orders AS (select
(CASE
WHEN extract(MONTH FROM o.order_purchase_timestamp)=1
THEN 'Jan'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=2
THEN 'Feb'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=3
THEN 'March'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=4
THEN 'April'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=5
THEN 'May'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=6
THEN 'June'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=7
THEN 'July'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=8
THEN 'Aug'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=9
THEN 'Sep'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=10
THEN 'Oct'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=11
THEN 'Nov'
ELSE 'Dec'
END) month,
p.payment_type,
oi.order_item_id
from `[Link]` o join `[Link]` p
on o.order_id=p.order_id left join `TargetEcommerce.order_items` oi
on o.order_id=oi.order_id)
select payment_type,month,count(order_item_id) as number_of_orders from
month_orders group by payment_type,month;
Month over month number of orders for each different payment type.
[Link] of payment installments and count of orders
with month_orders AS (select
(CASE
WHEN extract(MONTH FROM o.order_purchase_timestamp)=1
THEN 'Jan'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=2
THEN 'Feb'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=3
THEN 'March'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=4
THEN 'April'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=5
THEN 'May'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=6
THEN 'June'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=7
THEN 'July'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=8
THEN 'Aug'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=9
THEN 'Sep'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=10
THEN 'Oct'
WHEN extract(MONTH FROM o.order_purchase_timestamp)=11
THEN 'Nov'
ELSE 'Dec'
END) month,
p.payment_installments
from `[Link]` o join `[Link]` p
on o.order_id=p.order_id)
select payment_installments,count(*) as number_of_orders from month_orders group by
payment_installments order by count(*) desc;
1 installment has highest number of orders