WITH CustomerRatings AS (
SELECT
customer_id,
AVG(rating) AS avg_rating
FROM
wlmt_reviews
GROUP BY
customer_id
)
SELECT
CASE
WHEN avg_rating >= 4 THEN 'Promoter'
ELSE 'Neutral' END AS segment,
COUNT(*) AS customer_count,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM CustomerRatings),2) AS percentage
FROM
CustomerRatings
GROUP BY
segment
ORDER BY
percentage DESC;
WITH FiveRatedseries AS
(
SELECT
c.series_id,
series_name,
COUNT(DISTINCT r.profile_id) AS TotalProfileCount,
AVG(rating) AS AvgRating
FROM content c
INNER JOIN series s ON s.series_id=c.series_id
INNER JOIN reviews_ntf r ON r.content_id=c.content_id
GROUP BY c.series_id,
series_name
)
SELECT series_name,TotalProfileCount,ROUND(AvgRating,2)AS AvgRating
FROM FiveRatedseries
WHERE TotalProfileCount >= 5
ORDER BY AvgRating DESC
LIMIT 3
WITH CustomerData AS(
SELECT customer_id,
oz.order_id,
product_id,
SUM(quantity) AS purchase_quantity,
SUM(quantity) - LAG(SUM(quantity)) OVER(PARTITION BY customer_id,product_id ORDER BY order_id) quantity_diff
FROM orders_amz oz
INNER JOIN orderdetails o ON o.order_id=oz.order_id
GROUP BY customer_id,oz.order_id, product_id)
SELECT c.customer_id,
p.product_id,
product_name
FROM products_amz p
INNER JOIN CustomerData c on c.product_id=p.product_id
ORDER BY quantity_diff DESC
LIMIT 1
WITH MonthlySales AS (
SELECT
p.product_name,
s.store_id,
SUM(s.units_sold) as units_sold,
SUM(s.units_sold) OVER (PARTITION BY p.product_id) as total_units,
RANK() OVER (PARTITION BY p.product_id ORDER BY SUM(s.units_sold) DESC) as store_rank
FROM
product p
JOIN
sales s ON p.product_id = s.product_id
WHERE
YEAR(p.launch_date)=2022
AND s.sale_date BETWEEN p.launch_date AND DATE_ADD(p.launch_date, INTERVAL 1 MONTH)
GROUP BY
p.product_name, s.store_id
)
SELECT
ms.product_name,
st.location_city,
st.location_country,
ms.units_sold,
ms.total_units,
ROUND(100.0 * ms.units_sold / ms.total_units, 2) as store_influence_percentage
FROM
MonthlySales ms
JOIN
stores st ON ms.store_id = st.store_id
WHERE
ms.store_rank = 1
ORDER BY
store_influence_percentage DESC;
SELECT
p.product_name,
p.category,
SUM(s.units_sold) AS total_quantity_sold,
SUM(s.units_sold*s.unit_price) AS total_revenue
FROM product p
INNER JOIN sales s ON s.product_id=p.product_id
WHERE EXTRACT(YEAR FROM s.sale_date) = 2023
GROUP BY p.product_name
ORDER BY total_quantity_sold DESC
LIMIT 5
