SELECT product_id, category,
SUM(quantity) AS total_sold,
AVG(price) AS avg_price,
COUNT(*) AS order_count
FROM sales_data
WHERE sale_date >= '2025-01-01'
GROUP BY product_id, category
ORDER BY total_sold DESC;
SELECT customer_id,
COUNT(order_id) AS orders,
SUM(revenue) AS total_rev,
RANK() OVER (
ORDER BY total_rev DESC
) AS rev_rank
FROM orders o
JOIN customers c
ON o.cust_id = c.id
GROUP BY customer_id;
WITH monthly AS (
SELECT DATE_TRUNC('month',
sale_date) AS mon,
SUM(revenue) AS m_rev
FROM sales
GROUP BY 1)
SELECT mon, m_rev,
LAG(m_rev) OVER (
ORDER BY mon) AS prev
FROM monthly;
SELECT * FROM (
SELECT region, product,
revenue,
ROW_NUMBER() OVER (
PARTITION BY region
ORDER BY revenue DESC
) AS rn
FROM all_sales
) ranked WHERE rn <= 5;
CREATE EXTERNAL TABLE retail(
product_id STRING,
category STRING,
sale_date DATE,
quantity INT,
revenue DECIMAL(10,2))
PARTITIONED BY (year STRING)
STORED AS PARQUET
LOCATION 's3://retail/';
SELECT category,
COUNT(DISTINCT id) AS items,
ROUND(AVG(price),2) avg_p,
SUM(qty) AS total_units
FROM inventory
WHERE status = 'active'
GROUP BY category
HAVING SUM(qty) > 100;
SELECT product_id, category,
SUM(quantity) AS total_sold,
AVG(price) AS avg_price,
COUNT(*) AS order_count
FROM sales_data
WHERE sale_date >= '2025-01-01'
GROUP BY product_id, category
ORDER BY total_sold DESC;
SELECT customer_id,
COUNT(order_id) AS orders,
SUM(revenue) AS total_rev,
RANK() OVER (
ORDER BY total_rev DESC
) AS rev_rank
FROM orders o
JOIN customers c
ON o.cust_id = c.id
GROUP BY customer_id;
WITH monthly AS (
SELECT DATE_TRUNC('month',
sale_date) AS mon,
SUM(revenue) AS m_rev
FROM sales
GROUP BY 1)
SELECT mon, m_rev,
LAG(m_rev) OVER (
ORDER BY mon) AS prev
FROM monthly;
SELECT * FROM (
SELECT region, product,
revenue,
ROW_NUMBER() OVER (
PARTITION BY region
ORDER BY revenue DESC
) AS rn
FROM all_sales
) ranked WHERE rn <= 5;
CREATE EXTERNAL TABLE retail(
product_id STRING,
category STRING,
sale_date DATE,
quantity INT,
revenue DECIMAL(10,2))
PARTITIONED BY (year STRING)
STORED AS PARQUET
LOCATION 's3://retail/';
SELECT category,
COUNT(DISTINCT id) AS items,
ROUND(AVG(price),2) avg_p,
SUM(qty) AS total_units
FROM inventory
WHERE status = 'active'
GROUP BY category
HAVING SUM(qty) > 100;
