CREATE TABLE car_sales (
        id INT PRIMARY KEY,
        model VARCHAR(255),
        period INT,
        country VARCHAR(255),
        purchase_type VARCHAR(255),
        version VARCHAR(255),
        price DECIMAL(10, 2),
        gross_profit DECIMAL(10, 2)
    );

select * from car_sales limit 5;

    select count(id) total_orders from car_sales;

    select concat('$',sum(gross_profit)) gross_profit from car_sales;

    select distinct country from car_sales;

    select distinct model from car_sales;

select model, concat('$',sum(gross_profit)) as profit from car_sales
    group by model
    order by profit desc;

with country_profit as(
    	select country, sum(gross_profit) as profit from car_sales
    	group by country
    	order by profit desc
    )
    
    select country, concat('$', profit) as profit from country_profit;

with top_3_preferred_version as (
    	
    select country, version, count(version) qty_bought,
    row_number() over(partition by country) as rnk
    	from car_sales
    group by country, version
    order by country, qty_bought
    )
    
    select country, version, qty_bought
    from top_3_preferred_version
    where rnk <= 3
    order by country, qty_bought desc;

with country_preferred_car_model as (
    	
    select country, model, count(version) qty_bought,
    row_number() over(partition by country) as rnk
    	from car_sales
    group by country, model
    order by country, qty_bought
    )
    
    select country, model, qty_bought
    from country_preferred_car_model
    where rnk <= 3
    order by country, qty_bought desc;

    select distinct purchase_type from car_sales;

select purchase_type, count(id) as orders
    from car_sales
    group by purchase_type
    order by orders;

with monthly_profit as(
    	select gross_profit, 
    		case when right(period, 2) = '01' then 'January'
    		when right(period, 2) = '02' then 'February'
    		when right(period, 2) = '03' then 'March'
    		when right(period, 2) = '04' then 'April'
    		when right(period, 2) = '05' then 'May'
    		when right(period, 2) = '06' then 'June'
    		else period end as _month
    	from car_sales
    )
    select _month, sum(gross_profit) as 'monthly_profit($)'
    from monthly_profit
    group by _month
    order by 'monthly_profit($)' desc;