Everyday SQL (7)

There are many interesting usage of sql function, like case when which is super powerful. Hope you can find something interesting or useful from the code below. It’s used in the real working environment to fill a business data request.

with vvvos as (
select distinct
map.ccc_o_group_id os
from
base.dddd_o_mapping map
inner join base.dddd_ccc_o_groups groups
on map.ccc_o_group_id = groups.ccc_o_group_id
and is_dropship = ‘N’
where marketplace_id in (1,2,3,4,5,6,7)
)
,
uniqueTable as
(
select sss_id, case when max(aaa_ind)>0 then 1 else 0 end as aaa_ind1,case when max(bbb_ind)>0 then 1 else 0 end as bbb_ind1
from
(
select *, case when aaa=’true’ then 1 else 0 end as aaa_ind,case when bbb=’true’ then 1 else 0 end as bbb_ind from tableOOO
)
group by sss_id
)
,
sss_idccc as
(
select duilbo.sss_id, map.marketplace_id ,map.WAREHOUSE_ID ,sum(quantity) AS quantity
from ber.d_unified_ccc_c duilbo
left join
(
SELECT
OOUMM.MARKETPLACE_ID
, OW.WAREHOUSE_ID
, 1 AS DUMMY
FROM
base.warehouses OW
INNER JOIN base.dddd_o_mp_map OOUMM on OOUMM.unit_id = OW.unit_id
GROUP BY OOUMM.MARKETPLACE_ID
, OW.WAREHOUSE_ID
) map
on map.WAREHOUSE_ID = duilbo.WAREHOUSE_ID
inner join
(select distinct sss_id from tableOOO ) list
on list.sss_id = duilbo.sss_id
and list.marketplace = ‘US’
where
duilbo.SNAPSHOT_DAY = TO_DATE(‘20170729′,’YYYYMMDD’)
and duilbo.ccc_o_GROUP_ID in (select os from vvvos)
group by duilbo.sss_id, map.marketplace_id,map.WAREHOUSE_ID
)
,
vvv as
(
select marketplace_id, sss_id,WAREHOUSE_ID, sum(quantity) as on_hand_count,
case when marketplace_id = 1 and ( UPPER(WAREHOUSE_ID) like (‘o%’) or UPPER(WAREHOUSE_ID) like (‘u%’) or UPPER(WAREHOUSE_ID) like (‘t%’) or UPPER(WAREHOUSE_ID) = ‘TPF2’) THEN ‘F’
WHEN marketplace_id = 2 and ( UPPER(WAREHOUSE_ID) like (‘t%’) or UPPER(WAREHOUSE_ID) like (‘d%’) or UPPER(WAREHOUSE_ID) like (‘a%’) ) THEN ‘F’
WHEN marketplace_id in (1,3) and ( UPPER(WAREHOUSE_ID) like (‘c%’) or UPPER(WAREHOUSE_ID) like (‘c%’) or UPPER(WAREHOUSE_ID) like (‘b%’) or UPPER(WAREHOUSE_ID) like (‘SU%’) or UPPER(WAREHOUSE_ID) = ‘CVG8’ or UPPER(WAREHOUSE_ID) = ‘IVSA’) THEN ‘F’
WHEN marketplace_id IN (5,6,7) and ( UPPER(WAREHOUSE_ID) like (‘a%’) or UPPER(WAREHOUSE_ID) like (‘u%’) or UPPER(WAREHOUSE_ID) like (‘z%’) ) THEN ‘F’
else ‘NotF’ end as FVV
from sss_idccc
where quantity > 0
group by marketplace_id, sss_id,WAREHOUSE_ID
)

select vvv.marketplace_id, u.aaa_ind1,u.bbb_ind1, count(distinct vvv.sss_id) as distinct_sss_id_count, sum(vvv.on_hand_count) as quantity, sum(dim.average_cubein_each*vvv.on_hand_count) as total_cubein from vvv
inner join
(
select sss_id,marketplace_id, sum(volume_each)/count(sss_id) as average_cubein_each
from
(select sss_id,marketplace_id, round(PKG_HEIGHT,2)*round(PKG_LENGTH,2)*round(PKG_WIDTH,2) as volume_each
from ber.d_sss_ids_marketplace_attributes) dima
where sss_id in (select distinct sss_id from tableOOO )
group by sss_id, marketplace_id
) dim
on vvv.sss_id=dim.sss_id and vvv.marketplace_id=dim.marketplace_id
inner join uniqueTable u
on vvv.sss_id=u.sss_id
where vvv.FVV not in (‘F’) ——excluding F
group by vvv.marketplace_id, u.aaa_ind1,u.bbb_ind1