/*+ ETLM {
depend:{
add:[
{
name:"BOOKER_EXT.D_DAILY_BUYABLE_OFFER_LISTINGS"
},
{
name:"BOOKER.D_MP_ASINs"
},
{
name:"booker_ext.MX_D_DAILY_GV_SESSIONS"
}
]
}
}
*/
SELECT DISTINCT asin ,
                Min(ag_price)      AS price ,
                Min(ag_msrp)       AS msrp ,
                Avg(shipping_cost) AS shipping ,
                Min(price)         AS min_price ,
                product_family ,
                gl ,
                Count (DISTINCT glance_views) AS gv
FROM            (
                           SELECT
                                      /*+ use_hash(bol, dmp)*/
                                      dmp.asin ,
                                      bol.eod_our_price AS ag_price ,
                                      bol.msrp_price    AS ag_msrp ,
                                      Max(snapshot_day) ,
                                      bol.shipping_cost ,
                                      bol.eod_our_price + bol.shipping_cost AS price ,
                                      mddgs.product_family                  AS product_family ,
                                      mddgs.gl_product_group                AS gl ,
                                      mddgs.glance_view_count               AS glace_views
                           FROM       booker_ext.d_daily_buyable_offer_listings bol
                           INNER JOIN booker.d_mp_asins dmp
                           ON         dmp.asin = bol.asin
                           INNER JOIN booker_ext.mx_d_daily_gv_sessions mddgs
                           ON         bol.asin = mddgs.asin
                           WHERE      bol.region_id = 1
                           AND        bol.snapshot_day = To_date('{RUN_DATE_YYYY/MM/DD}','YYYY/MM/DD')
                           AND        bol.marketplace_id = 771770
                           AND        bol.merchant_customer_id NOT IN (21277875605,
                                                                       8833336105)
                           AND        bol.offering_end_datetime IS NULL
                           AND        dmp.marketplace_id = 771770
                           AND        dmp.region_id = 1
                           AND        dmp.gl_product_group IN ({FREE_FORM})
                           GROUP BY   dmp.asin ,
                                      bol.eod_our_price ,
                                      bol.shipping_cost ,
                                      bol.msrp_price ,
                                      mddgs.product_family ,
                                      mddgs.gl_product_group ,
                                      mddgs.glance_view_count)
GROUP BY        asin,