You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Top 10 product categories most ordered by customers
%%sql
WITH customer_items AS (SELECT*FROM customers
JOIN orders USING(customer_id)
JOIN order_items USING(order_id)
JOIN products USING(product_id))
SELECT product_category,
SUM(order_item_id) AS units
FROM customer_items
GROUP BY product_category
ORDER BY units DESCLIMIT10
%%sql
WITH payment AS(SELECT customer_state,
payment_type
FROM orders AS od
JOIN customers AS cu USING(customer_id)
JOIN order_payments AS pay USING(order_id))
SELECT customer_state,
payment_type,
COUNT(*)
FROM payment
WHERE payment_type ='credit_card'GROUP BY customer_state, payment_type
ORDER BY count DESC
Proportion of customers paying in more than one installment by state
%%sql
SELECT customer_state,
count,
(count/total_order) AS proportion
FROM(WITH payment AS(SELECT customer_state,
payment_installments
FROM orders AS od
JOIN customers AS cu USING(customer_id)
JOIN order_payments AS pay USING(order_id)
WHERE payment_installments >1)
SELECT customer_state,
COUNT(payment_installments)::realAS count,
(SELECTCOUNT(order_id) FROM orders)::realAS total_order
FROM payment
GROUP BY customer_state
ORDER BY count DESC) AS g2
WHERE count >1000ORDER BY proportion DESC