Olist: Penetrating The Brazilian Market

query = """
WITH orders_by_state AS(
    SELECT
        customers.customer_state as state,
        SUM(payments.payment_installments) AS tot_order
    FROM order_payments AS payments
    JOIN orders ON orders.order_id = payments.order_id
    JOIN customers ON customers.customer_id = orders.customer_id
    GROUP BY customers.customer_state
    ORDER BY SUM(payments.payment_installments) DESC
)

SELECT
    *,
    SUM(tot_order) OVER (ORDER BY tot_order DESC) / (SELECT SUM(tot_order)
                                                    FROM orders_by_state) AS cum_share
FROM orders_by_state
"""

orders_by_state = pd.read_sql(query, engine)
orders_by_state.head()

orders_by_state[['tot_order', 'gdp']].corr()

top_8 = orders_by_state[orders_by_state['cum_share']<0.85]
top_8

#ANOVA test

query = """
SELECT
    SUM(payment_value::NUMERIC) AS order_value,
    customers.customer_state AS state
FROM orders
JOIN customers ON customers.customer_id = orders.customer_id
JOIN order_payments AS payments ON payments.order_id = orders.order_id
GROUP BY customers.customer_state, orders.order_id
"""

df = pd.read_sql(query, engine)
df.head()

states = []
for state in df['state'].unique():
    states.append(df.loc[df['state']==state, 'order_value'].values)

f_oneway(*states)

# The order value is influenced by the state

query = """
WITH order_values AS(
    SELECT
        SUM(payment_value::NUMERIC) AS order_value,
        customers.customer_state AS state
    FROM orders
    JOIN customers ON customers.customer_id = orders.customer_id
    JOIN order_payments AS payments ON payments.order_id = orders.order_id
    GROUP BY customers.customer_state, orders.order_id
)

SELECT
    state,
    AVG(order_value) as mean_order_value,
    STDDEV(order_value) as std_order_value
FROM order_values
GROUP BY state
ORDER BY AVG(order_value) DESC
"""

order_value_by_states = pd.read_sql(query, engine)
order_value_by_states.head()

df_states = pd.merge(orders_by_state, order_value_by_states, on='state').rename(columns={'states':'state_name'})
df_states.head()

df_states[['gdp', 'mean_order_value']].corr()

ci_order_value = ci_order_value.sort_values('lo_bound', ascending=False).reset_index(drop=True)
ci_order_value.head()

ci_order_value.head(9)

top_8

plt.barh(ci_order_value['PB_vs'], ci_order_value['up_bound']-ci_order_value['lo_bound'], left=ci_order_value['lo_bound'])
plt.title('Conf. Int. Avg Order Value by State')
plt.show()

# ANOVA test

query = '''
SELECT
	customers.customer_state AS state,
	CASE
		WHEN COUNT(*)>1 THEN 1
		ELSE 0
	END AS is_recurring
FROM orders
JOIN customers ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_state, customers.customer_unique_id
'''

anova_t_recur = pd.read_sql(query, engine)
anova_t_recur.head()

states = []
for state in anova_t_recur['state'].unique():
    states.append(anova_t_recur.loc[df['state']==state, 'is_recurring'].values)

f_oneway(*states)

query = '''
WITH recurring_tab AS(
	SELECT
		customers.customer_state AS state,
		COUNT(*) AS num_orders,
		CASE
			WHEN COUNT(*)>1 THEN 1
			ELSE 0
		END AS is_recurring
	FROM orders
	JOIN customers ON customers.customer_id = orders.customer_id
	GROUP BY customers.customer_state, customers.customer_unique_id
),

pi_table AS(
    SELECT
        state,
        COUNT(*) AS size,
        SUM(is_recurring) AS recurring_size,
        ROUND(SUM(is_recurring)::NUMERIC/COUNT(*)::NUMERIC, 4) AS pi
    FROM recurring_tab
    GROUP BY state
)

SELECT *
FROM pi_table
WHERE size*pi*(1-pi) > 15
ORDER BY pi DESC
'''

recurring_by_state = pd.read_sql(query, engine)
recurring_by_state.head()

plt.barh(ci_proportion_value['RJ_vs'], ci_proportion_value['up_bound']-ci_proportion_value['lo_bound'], left=ci_proportion_value['lo_bound'])
plt.title('Conf. Int. % Recurring Customers by State')
plt.show()

query = '''
SELECT
    AVG(payment_value::NUMERIC) AS avg_order_value,
	COUNT(*) AS num_transactions,
	STDDEV(payment_value::NUMERIC) AS std,
	payment_type
FROM order_payments
WHERE payment_value > '0€'
    AND payment_sequential = 1
GROUP BY payment_type
'''

pay_types = pd.read_sql(query, engine)
pay_types

plt.barh(ci_paym_type_value['CC_vs'], ci_paym_type_value['up_bound']-ci_paym_type_value['lo_bound'], left=ci_paym_type_value['lo_bound'])
plt.title('Conf. Int. Order Value by Payment Type')
plt.xlim(-1,15)
plt.show()

query='''
WITH review_vs_delivery AS(
	SELECT
		EXTRACT(DAY FROM orders.order_estimated_delivery_date - orders.order_delivered_customer_date) AS days_in_advance,
		reviews.review_score
	FROM orders
	JOIN order_reviews AS reviews ON reviews.order_id = orders.order_id
	WHERE (orders.order_estimated_delivery_date - orders.order_delivered_customer_date) IS NOT NULL
)

SELECT
    CASE
        WHEN days_in_advance > 0 THEN 'early'
        WHEN days_in_advance = 0 THEN 'on_time'
        ELSE 'delay'
    END AS delivery_status,
	STDDEV(review_score) as std,
	COUNT(*) AS frequency,
    ROUND(AVG(review_score),2) AS average_review_score
FROM 
    review_vs_delivery
GROUP BY 
    CASE
        WHEN days_in_advance > 0 THEN 'early'
        WHEN days_in_advance = 0 THEN 'on_time'
        ELSE 'delay'
    END
'''

delivery_satisf = pd.read_sql(query, engine)
delivery_satisf

plt.barh(ci_delivery_satisf['on_time_vs'], ci_delivery_satisf['up_bound']-ci_delivery_satisf['lo_bound'], left=ci_delivery_satisf['lo_bound'])
plt.title('Conf. Int. Delivery vs Satisfaction')
plt.xlim(-1,2.5)
plt.show()

query = '''
WITH review_vs_delivery AS(
	SELECT
		EXTRACT(DAY FROM orders.order_estimated_delivery_date - orders.order_delivered_customer_date) AS days_in_advance,
		reviews.review_score
	FROM orders
	JOIN order_reviews AS reviews ON reviews.order_id = orders.order_id
	WHERE (orders.order_estimated_delivery_date - orders.order_delivered_customer_date) IS NOT NULL
),

reviews_delivery_bin AS(
	SELECT
		*,
		width_bucket(days_in_advance, -20, 20, 20) AS bin
	FROM review_vs_delivery
)

SELECT
	bin,
	MAX(days_in_advance) AS high_bound,
	MIN(days_in_advance) AS low_bound,
	AVG(review_score) AS avg_stars
FROM reviews_delivery_bin
GROUP BY bin
ORDER BY bin
'''

satisf_delivery_trend = pd.read_sql(query, engine)
satisf_delivery_trend.head()

plt.plot(satisf_delivery_trend['low_bound'], satisf_delivery_trend['avg_stars'])
plt.title('Satisfaction Trend vs. Delivery Time')
plt.show()

query = '''
SELECT
	CASE
		 WHEN sellers.seller_city = customers.customer_city THEN 'km 0'
		 WHEN sellers.seller_state = customers.customer_state THEN 'local'
		 ELSE 'not local'
	END,
	COUNT(*) AS frequency
FROM orders
LEFT JOIN order_items AS items ON items.order_id = orders.order_id
JOIN customers ON customers.customer_id = orders.customer_id
JOIN sellers ON sellers.seller_id = items.seller_id
GROUP BY CASE
		 	WHEN sellers.seller_city = customers.customer_city THEN 'km 0'
			WHEN sellers.seller_state = customers.customer_state THEN 'local'
		 	ELSE 'not local'
		 END;
'''

pd.read_sql(query, engine)

query = '''
WITH distances AS(
    SELECT
        ACOS(
            GREATEST(
                LEAST(
                    SIN(RADIANS(geo_sellers.geolocation_lat)) * SIN(RADIANS(geo_cust.geolocation_lat)) +
                    COS(RADIANS(geo_sellers.geolocation_lat)) * COS(RADIANS(geo_cust.geolocation_lat)) *
                    COS(RADIANS(geo_cust.geolocation_lng) - RADIANS(geo_sellers.geolocation_lng)),
                    1
                ),
                -1
            )
        ) * 6371 AS distance
    FROM orders
    LEFT JOIN order_items AS items ON items.order_id = orders.order_id
    JOIN customers ON customers.customer_id = orders.customer_id
    JOIN sellers ON sellers.seller_id = items.seller_id
    JOIN geolocation as geo_sellers ON geo_sellers.geolocation_zip_code_prefix = sellers.seller_zip_code_prefix
    JOIN geolocation as geo_cust ON geo_cust.geolocation_zip_code_prefix = customers.customer_zip_code_prefix
)

SELECT
    AVG(distance) AS avg_distance,
    PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY distance) AS median_distance
FROM distances;
'''

pd.read_sql(query, engine)

query = '''
SELECT
	translat.product_category_name_english AS category,
	COUNT(*) AS frequency
FROM products
JOIN product_category_name_translation AS translat ON translat.product_category_name = products.product_category_name
JOIN order_items AS items ON items.product_id = products.product_id
GROUP BY translat.product_category_name_english
ORDER BY COUNT(*) DESC;
'''

pd.read_sql(query, engine).head()

query = '''
WITH sold_togheter AS(
	SELECT
		translat.product_category_name_english AS category,
		items.product_id AS product_id,
		items.order_id AS order_id,
		MAX(items.order_item_id) OVER (PARTITION BY items.order_id) AS num_items
	FROM products
	JOIN product_category_name_translation AS translat ON translat.product_category_name = products.product_category_name
	JOIN order_items AS items ON items.product_id = products.product_id
	ORDER BY items.order_item_id DESC
),

categories_sold_togheter AS(
	SELECT
		order_id,
		category,
		num_items,
		COUNT(*) AS num_category
	FROM sold_togheter
	WHERE num_items > 1
	GROUP BY order_id, category, num_items
	ORDER BY num_items DESC
)

SELECT
	SUM(CASE WHEN num_items=num_category THEN 1 ELSE 0 END) AS one_cat_orders,
	COUNT(DISTINCT order_id) as tot_orders
FROM categories_sold_togheter;
'''

pd.read_sql(query, engine)