# STEP1 计算各类别平均总耗电量
with a1 as (
select category,sum(usage_kwh)/count(*) as avg_category
from smart_devices a 
left join energy_logs b 
on a.device_id=b.device_id and log_timestamp between '2025-01-01 00:00:00' and '2025-01-31 23:59:59'
group by category),

# STEP2 筛选出总耗电量>类别平均总耗电量的设备，并初步得出所需字段
a2 as (
select device_name,
location,
sum(usage_kwh) as total_usage,
case when sum(usage_kwh)>=50 then 'High Load' else 'Normal' end as efficiency_level
from smart_devices a 
left join energy_logs b 
on a.device_id=b.device_id and log_timestamp between '2025-01-01 00:00:00' and '2025-01-31 23:59:59' 
left join a1
using(category)
group by device_name,location
having sum(usage_kwh)>max(avg_category)
order by total_usage desc,a.device_id)

# STEP3 调整location字段格式
select device_name,
upper(replace(location,' ','_')) as location_code,
total_usage,
efficiency_level
from a2

个人认为难点：

1、容易忽略log_timestamp between '2025-01-01 00:00:00' and '2025-01-31 23:59:59' 而非 '2025-01-01' and '2025-01-31'

2、由于在a2中先用device_id进行了order by，因此需要带上device_id去group by