Project-for-avocado-sales-team-in-USA.md

🥑🥑🥑 Inspecting Avocado sales in USA (New York vs San Francisco)

**🔑 Tool: ClickHouse via Redash🔑**

-- Task 1. Avocado NY, SF type share.

SELECT region, type, total

FROM

(select region, type, SUM(total_volume) as total

from

(select region, type, SUM(total_volume) AS total_volume

from default.avocado


GROUP BY region, type

ORDER BY total_volume DESC)

GROUP BY region, type

ORDER BY total DESC

)

WHERE region IN ('SanFrancisco', 'NewYork')

GROUP BY region, type, total

ORDER BY total DESC

Output:

-- Task 2. NY, SF total_volume time line by month.

SELECT toStartOfMonth(date) AS by_month, year, region, SUM(total_volume) AS total_v

FROM

(select date, year, region, type, SUM(total_volume) AS total_volume

from default.avocado

--WHERE region != 'TotalUS' 

GROUP BY date, year, region, type

ORDER BY total_volume DESC)

WHERE region IN ('SanFrancisco', 'NewYork')

GROUP BY toStartOfMonth(date), year, region

ORDER BY total_v DESC

Output:

-- Task 3. Average price per unit in compare with total US for New York & San Francisco by month & year.

SELECT toStartOfMonth(date) AS by_month, region, AVG(average_price) AS total_avg

FROM 

(select date, year, region, type, average_price

from default.avocado)

WHERE region IN ('SanFrancisco', 'NewYork', 'TotalUS')

GROUP BY toStartOfMonth(date) AS by_month, region

ORDER BY total_avg DESC

Output:

-- Task 4. Organic type sales volume by month & week.

SELECT CAST(date AS date), total_volume, region, 
SUM(total_volume) OVER(PARTITION BY region ORDER BY year ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total

FROM avocado

WHERE region IN ('NewYork', 'SanFrancisco') AND type = 'organic'

Output:

-- Task 5. Final DASHBOARD.

https://redash.lab.karpov.courses/dashboards/4264-sales_team_avocado_usa