SELECT c.customer_state AS state, g.geolocation_city,
ROUND(AVG((julianday(o.order_delivered_customer_date) - julianday(o.order_purchase_timestamp)) * 24), 2) AS average_delivery_time
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN geolocation g ON g.geolocation_state = c.customer_state
GROUP BY c.customer_state, g.geolocation_city
ORDER BY average_delivery_time;
WITH product_sold AS (
SELECT
product_category_name_translation.product_category_name,
product_category_name_translation.product_category_name_english AS product_category,
SUM(order_items.order_item_id) AS sales_count
FROM
product_category_name_translation
JOIN
products ON product_category_name_translation.product_category_name = products.product_category_name
JOIN
order_items ON products.product_id = order_items.product_id
GROUP BY
product_category_name_translation.product_category_name_english
ORDER BY sales_count DESC
),
product_popularity AS (
SELECT *,
CASE
WHEN sales_count = 0 THEN 'No Sales'
WHEN sales_count >= 1 AND sales_count < 1999 THEN 'Low Demand'
WHEN sales_count >= 2000 AND sales_count < 4500 THEN 'Medium Demand'
ELSE 'High Demand'
END AS product_demand
FROM
product_sold
)
SELECT *
FROM
product_popularity
WHERE
product_demand = 'High Demand'
WITH product_pairs AS (
SELECT
CASE WHEN pp1.product_category < pp2.product_category THEN pp1.product_category ELSE pp2.product_category END AS product1,
CASE WHEN pp1.product_category < pp2.product_category THEN pp2.product_category ELSE pp1.product_category END AS product2,
COUNT(*) AS frequency
FROM
order_items oi1
JOIN
order_items oi2 ON oi1.order_id = oi2.order_id AND oi1.product_id < oi2.product_id
JOIN
products p1 ON oi1.product_id = p1.product_id
JOIN
products p2 ON oi2.product_id = p2.product_id
JOIN
product_popularity pp1 ON p1.product_category_name = pp1.product_category_name
JOIN
product_popularity pp2 ON p2.product_category_name = pp2.product_category_name
GROUP BY
CASE WHEN pp1.product_category < pp2.product_category THEN pp1.product_category ELSE pp2.product_category END,
CASE WHEN pp1.product_category < pp2.product_category THEN pp2.product_category ELSE pp1.product_category END
HAVING
product1 <> product2
ORDER BY
frequency DESC
)
SELECT *
FROM
product_pairs
SELECT c.customer_state AS state, p.product_category_name AS product_category,
COUNT(DISTINCT c.customer_id) AS total_customers
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN customers c ON o.customer_id = c.customer_id
JOIN geolocation g ON c.customer_zip_code_prefix = g.geolocation_zip_code_prefix
WHERE p.product_category_name = 'cama_mesa_banho'
GROUP BY c.customer_state, p.product_category_name
ORDER BY total_customers DESC;
SELECT c.customer_state AS state, c.customer_city AS city, p.product_category_name
AS product_category, c.customer_id, MIN(g.geolocation_lat) AS latitude,
MIN(g.geolocation_lng) AS longitude
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN customers c ON o.customer_id = c.customer_id
JOIN geolocation g ON c.customer_zip_code_prefix = g.geolocation_zip_code_prefix
WHERE p.product_category_name = 'cama_mesa_banho'
GROUP BY c.customer_state, c.customer_city, c.customer_id,p.product_category_name
ORDER BY c.customer_state DESC, c.customer_id;
SELECT op.payment_type, COUNT(*) AS total_count
FROM order_payments op
JOIN orders o ON op.order_id = o.order_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.product_category_name = 'cama_mesa_banho'
GROUP BY op.payment_type
ORDER BY total_count DESC