# $1543. 产品名称格式修复
# https://leetcode-cn.com/problems/fix-product-name-format/

# SQL架构
Create table If Not Exists Sales (sale_id int, product_name varchar(30), sale_date date);
Truncate table Sales;
insert into Sales (sale_id, product_name, sale_date) values ('1', 'LCPHONE', '2000-01-16');
insert into Sales (sale_id, product_name, sale_date) values ('2', 'LCPhone', '2000-01-17');
insert into Sales (sale_id, product_name, sale_date) values ('3', 'LcPhOnE', '2000-02-18');
insert into Sales (sale_id, product_name, sale_date) values ('4', 'LCKeyCHAiN', '2000-02-19');
insert into Sales (sale_id, product_name, sale_date) values ('5', 'LCKeyChain', '2000-02-28');
insert into Sales (sale_id, product_name, sale_date) values ('6', 'Matryoshka', '2000-03-31');

# Write your MySQL query statement below
select
    lower(trim(product_name)) as product_name,
    left(sale_date, 7) as sale_date,
    count(*) as total
from
    Sales
group by
    lower(trim(product_name)),
    left(sale_date, 7)
order by
    lower(trim(product_name)),
    left(sale_date, 7);

# clean-up
drop table Sales;