SELECT /* Fix for bug # 4751315 */
prod.site_id site_id -- Required for the site dimension join
,prod.transaction_date transaction_date -- Required for join to the time dimension
,prod.leaf_categ_id leaf_categ_id -- Required for join to the product category dimension
,prod.product_id product_id -- Required for join to the product dimension
,prod.visitant_id visitant_id -- Daily Unique Visitors is calculated
-- by doing distinct of visitant_id
-- because this metric is non additive across dimensions
,prod.visit_id visit_id --Numerator for percent product vsists;its not aggregated
--since "one visit for product category needs to be derived"
,SUM(prod.no_visits) no_visits -- denominator for percent product visits
,SUM(prod.page_views) page_views -- Number of page views
,prod.orders_site_visits orders_site_visits -- for Browse to Buy (Numerator)
, prod.order_id order_id --for booked web order
, prod.qot_order_id qot_order_id --for cartconversion numerator
,prod.cart_id cart_id --for carts
,prod.currency_cd_f currency_cd_f --Currency dimension
,SUM(booked_amt_f) booked_amt_f
,SUM(booked_amt_g) booked_amt_g
,SUM(booked_amt_g1) booked_amt_g1
,COUNT(prod.no_visits) d_no_visits
,COUNT(prod.page_views) d_page_views
,COUNT(booked_amt_f) d_booked_amt_f
,COUNT(booked_amt_g) d_booked_amt_g
,COUNT(booked_amt_g1) d_booked_amt_g1
,COUNT(*) d_tot_col
,(CASE
WHEN GROUPING (time.report_date_julian) = 0
THEN time.report_date_julian
WHEN GROUPING (time.week_id) = 0
THEN time.week_id
WHEN GROUPING (time.ent_period_id) = 0
THEN time.ent_period_id
WHEN GROUPING (time.ent_qtr_id) = 0
THEN time.ent_qtr_id
ELSE time.ent_year_id
END
) time_id
,(CASE
WHEN GROUPING (time.report_date_julian) = 0
THEN 1
WHEN GROUPING (time.week_id) = 0
THEN 16
WHEN GROUPING (time.ent_period_id) = 0
THEN 32
WHEN GROUPING (time.ent_qtr_id) = 0
THEN 64
ELSE 128
END
) period_type_id
,time.ent_year_id
,time.ent_qtr_id
,time.ent_period_id
,time.report_date_julian
,time.week_id
,GROUPING_ID (prod.site_id
,prod.transaction_date
,prod.leaf_categ_id
,prod.product_id
,prod.visitant_id
,prod.visit_id
,prod.currency_cd_f
,prod. order_id
,prod. qot_order_id
,prod. cart_id
,prod.orders_site_visits
,time.ent_year_id
,time.ent_qtr_id
,time.ent_period_id
,time.week_id
,time.report_date_julian) d_gid
FROM
ibw_product_sum_mv prod
,FII.FII_TIME_DAY time
WHERE prod.transaction_date = time.report_date
GROUP BY
prod.site_id
,prod.transaction_date
,prod.leaf_categ_id
,prod.product_id
,prod.visitant_id
,prod.visit_id
,prod.currency_cd_f
,prod. order_id
,prod. qot_order_id
,prod. cart_id
,prod.orders_site_visits
,GROUPING SETS (time.ent_year_id,time.ent_qtr_id, time.ent_period_id, time.week_id,time.report_date_julian)
[Home] [Help]