Hỏi chatbot doanh số: 44 câu đã chấm, 40 câu mở rộng

with dau as (select khach_key, ngay_mua_dau from mart.bt_khach_mua_dau
             where nhom_khach = 'ca_nhan' and ngay_mua_dau between date '2026-07-01' and date '2026-07-31'),
kenh_dau as (select d.khach_key, min(k.nhom_kenh) as nhom_kenh from dau d
             join mart.bt_khach_ngay k on k.khach_key = d.khach_key and k.order_date = d.ngay_mua_dau and k.n_don_hda > 0
             group by 1),
quay as (select distinct d.khach_key from dau d
         join mart.bt_khach_ngay k on k.khach_key = d.khach_key and k.n_don_hda > 0
          and k.order_date > d.ngay_mua_dau and k.order_date <= d.ngay_mua_dau + 30)
select kd.nhom_kenh, count(*) as khach_moi, count(q.khach_key) as quay_lai_30_ngay,
       round(100.0 * count(q.khach_key) / count(*), 2) as pct
from kenh_dau kd left join quay q using (khach_key) group by 1

with pk as (select distinct khach_key, phan_khuc_thang from mart.bt_khach_ngay
            where order_date >= date '2026-08-01' and order_date < date '2026-09-01' and phan_khuc_thang in ('moi', 'cu'))
select pk.phan_khuc_thang, coalesce(p.category_name, '(không nhóm)') as nhom_hang, sum(s.doanh_thu) as dt
from mart.fact_sales s
join pk on pk.khach_key = md5(upper(trim(s.customer_code)))
left join mart.dim_product p on p.product_code = s.product_code
where not s.is_excluded and s.doc_kind = 'HDA' and s.order_date >= date '2026-08-01' and s.order_date < date '2026-09-01'
group by 1, 2 order by 1, 3 desc

with k as (select khach_key, bool_or(nhom_kenh = 'POS') co_pos, bool_or(nhom_kenh = 'ONLINE') co_onl,
                  sum(dt_ban_hda) dt from mart.bt_khach_ngay where nhom_khach = 'ca_nhan' group by 1)
select (co_pos and co_onl) as da_kenh, count(*) as so_khach, sum(dt) as dt,
       round(100.0 * sum(dt) / sum(sum(dt)) over (), 2) as pct from k group by 1

with t as (select g.province_name as tinh, s.nhom_kenh, sum(s.doanh_thu) dt,
                  count(distinct s.order_id) filter (where s.doc_kind = 'HDA') n
  from mart.fact_sales s
  join mart.dim_customer_bi c on upper(c.customer_code) = upper(s.customer_code)
  left join (select distinct province_code, province_name from mart.dim_geo) g on g.province_code = c.province_code
  where not s.is_excluded and c.is_identifiable and s.order_date >= date '2026-08-01' and s.order_date < date '2026-09-01' group by 1, 2)
select coalesce(tinh, '(không rõ tỉnh)') as tinh, sum(dt) as dt, round(sum(dt) / nullif(sum(n), 0)) as dt_moi_don,
       (array_agg(nhom_kenh order by dt desc))[1] as kenh_chinh
from t group by 1 order by 2 desc limit 5

select coalesce(c.membership, '(không hạng)') as hang_the, count(distinct upper(c.customer_code)) as so_khach,
       sum(s.doanh_thu) as dt,
       round(count(distinct s.order_id) filter (where s.doc_kind = 'HDA')::numeric
             / nullif(count(distinct upper(c.customer_code)), 0), 2) as don_moi_khach
from mart.fact_sales s join mart.dim_customer_bi c on upper(c.customer_code) = upper(s.customer_code)
where not s.is_excluded and c.is_identifiable
  and s.order_date > (select max(order_date) from mart.bt_kpi_ngay) - 365
group by 1 order by 3 desc

select coalesce(c.gender, '(không rõ)') as gioi_tinh, coalesce(p.category_name, '(không nhóm)') as nhom_hang,
       sum(s.doanh_thu) as dt
from mart.fact_sales s
join mart.dim_customer_bi c on upper(c.customer_code) = upper(s.customer_code)
left join mart.dim_product p on p.product_code = s.product_code
where not s.is_excluded and c.is_identifiable and s.order_date >= date '2026-08-01' and s.order_date < date '2026-09-01'
group by 1, 2 order by 1, 3 desc

select st.mien, p.category_name,
       sum(s.doanh_thu) filter (where s.doc_kind = 'HDA') as dt_ban,
       -sum(s.doanh_thu) filter (where s.doc_kind = 'HDF' and s.doanh_thu < 0) as tra_gop,
       round(100.0 * -sum(s.doanh_thu) filter (where s.doc_kind = 'HDF' and s.doanh_thu < 0)
             / nullif(sum(s.doanh_thu) filter (where s.doc_kind = 'HDA'), 0), 2) as ti_le_tra_gop
from mart.fact_sales s
join mart.dim_store st on st.store_code = s.store_code
left join mart.dim_product p on p.product_code = s.product_code
where not s.is_excluded and s.order_date >= date '2026-08-01' and s.order_date < date '2026-09-01' and st.loai_diem_ban = 'CUA_HANG' and p.category_name is not null
group by 1, 2 having sum(s.doanh_thu) filter (where s.doc_kind = 'HDA') >= 100000000
order by ti_le_tra_gop desc

with s as (select store_code,
       sum(dt) filter (where order_date < date '2026-08-01') dt7, sum(dt) filter (where order_date >= date '2026-08-01') dt8,
       sum(n_don_hda) filter (where order_date < date '2026-08-01') n7, sum(n_don_hda) filter (where order_date >= date '2026-08-01') n8,
       sum(dt_hda) filter (where order_date < date '2026-08-01') b7, sum(dt_hda) filter (where order_date >= date '2026-08-01') b8
  from mart.bt_kpi_ngay where nhom_kenh = 'POS' and order_date between date '2026-07-01' and date '2026-08-31' group by 1)
select store_code, dt8 - dt7 as chenh_dt, n8 - n7 as chenh_don,
       round(b8 / nullif(n8, 0)) - round(b7 / nullif(n7, 0)) as chenh_bill_tb
from s where dt7 > 0 order by chenh_dt limit 3

with t as (select s.product_code, sum(s.doanh_thu) dt,
       count(*) filter (where s.doc_kind = 'HDA' and s.line_kind = 'SALE') n_ban,
       count(*) filter (where s.doc_kind = 'HDA' and s.line_kind = 'SALE' and coalesce(s.discount_amt, 0) = 0) n_dung_gia,
       sum(s.discount_amt) filter (where s.doc_kind = 'HDA' and s.line_kind = 'SALE') ck,
       sum(s.price * s.qty) filter (where s.doc_kind = 'HDA' and s.line_kind = 'SALE') niem_yet,
       -sum(s.doanh_thu) filter (where s.doc_kind = 'HDF' and s.doanh_thu < 0) tra,
       sum(s.doanh_thu) filter (where s.doc_kind = 'HDA') dt_ban
  from mart.fact_sales s join mart.dim_product p on p.product_code = s.product_code
  where not s.is_excluded and p.category_name is not null and s.order_date >= date '2026-08-01' and s.order_date < date '2026-09-01' group by 1)
select product_code, dt, round(100.0 * n_dung_gia / nullif(n_ban, 0), 2) as pct_dung_gia,
       round(100.0 * ck / nullif(niem_yet, 0), 2) as pct_ck, round(100.0 * tra / nullif(dt_ban, 0), 2) as ti_le_tra_gop
from t order by dt desc limit 10

with l as (select distinct s.order_id, p.category_name from mart.fact_sales s
            join mart.dim_product p on p.product_code = s.product_code
            where not s.is_excluded and s.doc_kind = 'HDA' and s.line_kind = 'SALE' and p.category_name is not null and s.order_date >= date '2026-08-01' and s.order_date < date '2026-09-01'),
d as (select distinct order_id from l where category_name = 'DRESS')
select l.category_name as mua_kem, count(*) as so_don, round(100.0 * count(*) / (select count(*) from d), 2) as pct_don_co_vay
from l join d using (order_id) where l.category_name <> 'DRESS' group by 1 order by 2 desc limit 5

with t as (select s.store_code, s.salesperson_code, sum(s.discount_amt) ck, sum(s.price * s.qty) ny, count(*) n
  from mart.fact_sales s where not s.is_excluded and s.doc_kind = 'HDA' and s.line_kind = 'SALE' and s.order_date >= date '2026-08-01' and s.order_date < date '2026-09-01'
   and s.salesperson_code is not null group by 1, 2),
c as (select store_code, sum(ck) / nullif(sum(ny), 0) ck_ch from t group by 1)
select t.store_code, t.salesperson_code, round(100.0 * t.ck / nullif(t.ny, 0), 2) as ck_nv, round(100.0 * c.ck_ch, 2) as ck_cua_hang
from t join c using (store_code) where t.n >= 30
order by t.ck / nullif(t.ny, 0) - c.ck_ch desc limit 10

with n as (select order_date, nhom_kenh, sum(dt) dt from mart.bt_kpi_ngay
           where order_date >= date '2026-07-01' group by 1, 2)
select d.weekday_num, n.nhom_kenh, round(avg(n.dt)) as dt_tb_ngay
from n join mart.dim_date d on d.date_key = n.order_date group by 1, 2 order by 2, 1

with tt as (select p.order_id, bool_or(p.payment_code <> 'TT' and p.amount > 0) as tra_them
  from mart.fact_payment p join mart.fact_order_bi o on o.order_id = p.order_id
  where o.order_date >= date '2026-08-01' and o.order_date < date '2026-09-01'
  group by 1 having bool_or(p.payment_code = 'TT'))
select o.store_code, count(*) as so_don_tt, count(*) filter (where tt.tra_them) as co_tra_them
from tt join mart.fact_order_bi o on o.order_id = tt.order_id group by 1 order by 2 desc limit 10

select o.channel, coalesce(o.delivery_state, '(trống)') as trang_thai, count(*) as so_don,
       round(100.0 * count(*) / sum(count(*)) over (partition by o.channel), 2) as pct
from mart.fact_order_bi o
where not o.is_excluded and o.doc_kind = 'HDA' and o.channel in ('TIK', 'SPE')
  and o.order_date >= date '2026-08-01' and o.order_date < date '2026-09-01'
group by 1, 2 order by 1, 3 desc

select coalesce(p.category_name, '(không nhóm)') as nhom_hang, s.nhom_kenh,
       sum(s.doanh_thu) filter (where s.order_date >= date '2026-08-01') as t8_2026,
       sum(s.doanh_thu) filter (where s.order_date < date '2025-09-01') as t8_2025
from mart.fact_sales s left join mart.dim_product p on p.product_code = s.product_code
where not s.is_excluded
  and ((s.order_date >= date '2026-08-01' and s.order_date < date '2026-09-01')
    or (s.order_date >= date '2025-08-01' and s.order_date < date '2025-09-01'))
group by 1, 2 order by 1, 2