SQL中級 復習

業種別の売上分析

select
  business_category,
  sum(daily_revenue) as total_revenue,
  round(avg(daily_revenue)) as avg_revenue,
  max(daily_revenue) as max_revenue,
  min(daily_revenue) as min_revenue
from
  daily_retail_data
group by
  business_category
order by
  total_revenue desc;

月次売上トレンド

with
  monthly_sales as (
    select
      strftime ('%Y-%m', transaction_date) as year_month,
      sum(daily_revenue) as total_revenue,
      avg(daily_revenue) as avg_daily_revenue
    from
      daily_retail_data
    group by
      year_month
  )
select
  year_month,
  total_revenue,
  round(avg_daily_revenue) as avg_daily_revenue,
  round(
    (
      total_revenue - lag(total_revenue) over (
        order by
          year_month
      )
    ) * 100.0 / lag(total_revenue) over (
      order by
        year_month
    ),
    1
  ) as growth_rate
from
  monthly_sales
order by
  year_month;

業種別の売上構成比

with
  sales_by_category as (
    select
      business_category,
      sum(daily_revenue) as total_revenue
    from
      daily_retail_data
    group by
      business_category
  ),
  total_sales as (
    select
      sum(total_revenue) as grand_total
    from
      sales_by_category
  )
select
  business_category,
  total_revenue,
  round(
    total_revenue * 100.0 / (
      select
        grand_total
      from
        total_sales
    ),
    1
  ) as revenue_share,
  round(
    sum(total_revenue) over (
      order by
        total_revenue desc
    ) * 100.0 / (
      select
        grand_total
      from
        total_sales
    ),
    1
  ) as cumulative_share
from
  sales_by_category
order by
  revenue_share desc;