Everyday SQL (3)

–Common Table Expressions (not called Temporary table, many thanks to YZ to correct me, refer to http://www.sql-server-performance.com/2015/common-table-expressions-cte-developers/)

with T as (
select Product_ID,max(case when MEDIA_LEG_RESULT=’T’ then 1 else 0 end) as T_leg
from detail  data1
where NVL(disconnect_reason,’1′) NOT IN (‘SPAM’,’DUPLICATE’)
and connect_date_datetime BETWEEN to_date(‘2016-07-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) and to_date(‘2016-08-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
and data1.Location_id=1
group by Product_ID
)
–Using this CTE (Common Table Expressions )
select data2.PRODUCT_ID,T.T_leg
from contacts data2
left join T
on T.Product_ID=data2.Product_ID
left join CSMETRICS_DDL.DIM_CS_CATEGORYS crs

ON data2.CATEGORY_ID = crs.CATEGORY_ID AND data2.LOCATION_ID = crs.LOCATION_SKEY
where data2.PRODUCT_CREATION_DAY between to_date(‘2016-07-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) and to_date(‘2016-09-30 23:59:59’, ‘YYYY-MM-DD HH24:MI:SS’)
AND data2. STATUS = 1
and crs.HIERARCHY IN (‘D’)
AND data2.PRODUCT_TYPE_CODE =’UU’
and data2.LOCATION_ID=6767