Frequency_analysis_of_orders

Frequency Of Orders

%load_ext sql
import os

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

host = "localhost"
database = "olist"
user = "postgres"
password = "sql123"
connection_string = f"postgresql://{user}:{password}@{host}/{database}"
%sql $connection_string

'Connected: postgres@olist'

Frequency of orders delivered every month

%%sql
SELECT date_part('year', order_delivered_customer)::int AS "year",
       date_part('month', order_delivered_customer)::int AS "month",
       COUNT(*) AS "orders_delivered"
FROM orders
WHERE order_status = 'delivered'
GROUP BY year, month
ORDER BY year, month

 * postgresql://postgres:***@localhost/olist
26 rows affected.

year	month	orders_delivered
2016	10	205
2016	11	58
2016	12	4
2017	1	283
2017	2	1351
2017	3	2382
2017	4	1849
2017	5	3751
2017	6	3223
2017	7	3455
2017	8	4302
2017	9	3965
2017	10	4494
2017	11	4670
2017	12	7205
2018	1	6597
2018	2	5850
2018	3	6824
2018	4	7850
2018	5	7111
2018	6	6829
2018	7	5839
2018	8	8314
2018	9	56
2018	10	3
None	None	8

Top 5 cities with most number of orders

%%sql
SELECT cu.customer_state,
       cu.customer_city,
       COUNT(oi.order_id)
FROM orders AS oi
JOIN customers AS cu ON oi.customer_id = cu.customer_id
GROUP BY cu.customer_city, cu.customer_state
ORDER BY count DESC
LIMIT 5

 * postgresql://postgres:***@localhost/olist
5 rows affected.

customer_state	customer_city	count
SP	sao paulo	15540
RJ	rio de janeiro	6882
MG	belo horizonte	2773
DF	brasilia	2131
PR	curitiba	1521

Deliveries by hour

%%sql
SELECT date_part('hour', order_delivered_customer)::int AS "hour",
       COUNT(*) AS "orders_delivered"
FROM orders
WHERE order_status = 'delivered'
GROUP BY hour

 * postgresql://postgres:***@localhost/olist
25 rows affected.

hour	orders_delivered
0	2885
1	1515
2	649
3	260
4	187
5	198
6	269
7	396
8	779
9	1196
10	1798
11	2579
12	3651
13	4561
14	5644
15	6740
16	7901
17	8775
18	9639
19	9484
20	9157
21	7627
22	6142
23	4438
None	8

Average difference between estimated and actual delivery by month

%%sql
SELECT month,
       AVG(deviation_from_estimated)::real AS difference
FROM(SELECT EXTRACT(MONTH FROM order_purchase)::int AS month,
            EXTRACT('days' FROM order_estimated_delivery - order_delivered_customer)::int AS deviation_from_estimated
FROM orders
WHERE order_status = 'delivered') AS diff
GROUP BY month

 * postgresql://postgres:***@localhost/olist
12 rows affected.

month	difference
1	13.38496
2	9.620979
3	7.1721644
4	11.974069
5	11.7074995
6	16.024807
7	10.879737
8	9.200588
9	10.389063
10	12.356947
11	7.2666025
12	12.093943

Average difference between order and delivery time by state

%%sql
SELECT delivery_time.customer_state,
       AVG(delivery_time.delivery_in_days)::real AS avg_delivery_time_days
FROM(SELECT cu.customer_state,
            EXTRACT('days' FROM od.order_delivered_customer - od.order_purchase)::int AS delivery_in_days
     FROM ORDERS AS od
     JOIN customers AS cu ON od.customer_id = cu.customer_id
     WHERE order_status = 'delivered') AS delivery_time
GROUP BY customer_state
ORDER BY avg_delivery_time_days DESC

 * postgresql://postgres:***@localhost/olist
27 rows affected.

customer_state	avg_delivery_time_days
RR	28.975609
AP	26.731344
AM	25.986206
AL	24.040302
PA	23.316067
MA	21.117155
SE	21.02985
CE	20.817827
AC	20.6375
PB	19.953579
PI	18.993698
RO	18.91358
BA	18.8664
RN	18.824894
PE	17.965473
MT	17.59368
TO	17.226278
ES	15.33183
MS	15.191155
GO	15.150741
RJ	14.848583
RS	14.819237
SC	14.4751835
DF	12.509134
MG	11.542188
PR	11.526711
SP	8.298094