DROP DATABASE IF EXISTS mcol_5074_db;
CREATE DATABASE mcol_5074_db;
USE mcol_5074_db;
CREATE TABLE `accnt` (
`acct_id` varchar(128) NOT NULL DEFAULT 'None',
`created_dt` datetime NOT NULL,
`mtn` varchar(21) NOT NULL DEFAULT 'None',
`accts` tinyint(4) NOT NULL DEFAULT 0
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4;
insert into accnt values
(1, '2021-12-11', "Partner", 5),
(2, '2021-12-12', "Retail", 4),
(1, '2021-12-11', "Partner", 5),
(3, '2021-12-11', "Mid-Market", 3),
(5, '2021-12-11', "PFG", 4),
(5, '2021-12-11', "PFG", 4),
(5, '2021-12-11', "Person Focal Group", 4),
(5, '2021-12-11', "Person Focal Group", 4);
select
mtn,
accts,
DATE_FORMAT(created_dt, '%Y-%m-%d') act_created_dt,
case when da.mtn = 'Partner' then count( acct_id) end as Partner_active_accts,
case when da.mtn = 'Retail' then count( acct_id) end as Retail_active_accts,
case when da.mtn = 'Enterprise' then count( acct_id) end as ENT_active_accts,
case when da.mtn in ('PFG','Person Focal Group') then count( acct_id) end +
case when da.mtn = 'Person Focal Group' then count( acct_id) end as PFG_active_accounts
from accnt da group by mtn, accts, act_created_dt order by mtn;
mtn	accts	act_created_dt	Partner_active_accts	Retail_active_accts	ENT_active_accts	PFG_active_accounts
Mid-Market	3	2021-12-11	NULL	NULL	NULL	NULL
Partner	5	2021-12-11	2	NULL	NULL	NULL
Person Focal Group	4	2021-12-11	NULL	NULL	NULL	4
PFG	4	2021-12-11	NULL	NULL	NULL	NULL
Retail	4	2021-12-12	NULL	1	NULL	NULL