Hello, this works, but I need to use crosstab here
Could you help me?
WITH second_table as(
WITH first_table as(
SELECT *
from sh.sales s
JOIN sh.products p ON s.prod_id = p.prod_id
JOIN sh.customers c ON c.cust_id = s.cust_id
JOIN sh.countries co ON co.country_id = c.country_id
WHERE
co.country_region = 'Asia'
AND
p.prod_category_desc = 'Photo'
AND
EXTRACT (YEAR FROM s.time_id) = 2000
ORDER BY p.prod_name, s.time_id
)
SELECT
prod_name,
extract(quarter from time_id) as quarter,
sum(amount_sold) AS common_amount
FROM first_table
GROUP BY
first_table.time_id,
first_table.prod_name
ORDER BY first_table.prod_name)
SELECT second_table.prod_name, second_table.quarter, sum(second_table.common_amount) AS common_amount FROM second_table
GROUP BY second_table.prod_name, second_table.quarter
;