select * from ( select (
bannerid, caption, client_url, image_file, sponsorid, weight from select
V.bannerid, V.impressions, B.caption, B.client_url, B.image_file, s.sponsorid,
s.weight, row_number() over (partition by s.sponsorid order by s.weight desc) ranking FROM ( — This level gives me a list of banners sorted by least seen,and then by highest weight select valid.bannerid, valid.totalweight, count(I.timestamp) as impressions FROM ( — This level gets me a list of banners that are valid for display select b.bannerid, — Add up the weight from 4 sources. Banner weight, and weight for each data item they match decode( decode(bitand(u.STATE_BM1,b.STATE_BM1),0,0,1) + decode(bitand(u.STATE_BM2,b.STATE_BM2),0,0,1) + decode(bitand(u.STATE_BM3,b.STATE_BM3),0,0,1), 0,0,b.STATE_WT ) + decode(bitand(u.AGE_BM,b.AGE_BM),0,0,b.AGE_WT)+ decode(bitand(u.GENDER_BM,b.GENDER_BM),0,0,b.GENDER_WT)+ b.weight as totalweight from tgif.tbl_users u, tgif.tbl_banners b, tgif.tbl_bannerstats bs where — I only care about ME! u.userid= 1 — Don’t show inactive banners and b.inactive != 1 — Only show banners that are currently running and sysdate < b.end_date and sysdate >=b.start_date — Only get the type of banner i’m looking for and b.type= 3 — Join on the total stats, and only display banners that haven’t reached their per banner maximums and b.bannerid = bs.bannerid and ( b.max_impressions IS NULL OR bs.total_impressions < b.max_impressions ) and ( b.max_clicks IS NULL OR bs.total_clicks < b.max_clicks ) and ( b.max_conversions IS NULL OR bs.total_conversions < b.max_conversions ) — Ignore any banners that don’t match their demographics (ie, male banner won’t go to females) and ( b.AGE_BM IS NULL OR b.AGE_BM = 0 OR bitand(u.AGE_BM, b.AGE_BM) != 0 ) and ( b.GENDER_BM IS NULL OR b.GENDER_BM =0 OR bitand(u.GENDER_BM, b.GENDER_BM) != 0 ) and ( b.STATE_BM1 IS NULL OR b.STATE_BM1 =0 OR bitand(u.STATE_BM1, b.STATE_BM1) != 0 ) and ( b.STATE_BM2 IS NULL OR b.STATE_BM2 =0 OR bitand(u.STATE_BM2, b.STATE_BM2) != 0 ) and ( b.STATE_BM3 IS NULL OR b.STATE_BM3 =0 OR bitand(u.STATE_BM3, b.STATE_BM3) != 0 ) — But don’t show me any banners that I have already signed up and b.bannerid NOT IN ( SELECT B.bannerid FROM tgif.tbl_bannerconversions C, tgif.tbl_banners B, tgif.tbl_sponsors sp WHERE C.USERID=1 AND C.bannerid=B.bannerid AND B.sponsorid=sp.sponsorid — unless they have a conversion interval, and that interval has expired AND ( sp.conversion_interval = 0 OR sysdate > C.timestamp+sp.conversion_interval ) ) — Don’t show me any banners that have SPONSORS that have reached their maximums and b.sponsorid NOT IN ( — I believe this would be better done using HAVING clauses, but I can’t figure it out — Take the banners for a sponsor in the bannerstats table, and get the totals per sponsor — return anything that has reached it’s maximum select sponsorid FROM ( SELECT S.sponsorid, S.max_impressions, S.max_conversions, S.max_clicks, sum(total_impressions) as imps, sum(total_conversions) as convs, sum(total_clicks) as clicks FROM tgif.tbl_sponsors S, tgif.tbl_banners B, tgif.tbl_bannerstats bs WHERE S.sponsorid=B.sponsorid AND B.bannerid=bs.bannerid GROUP BY S.Sponsorid, S.max_impressions, S.max_conversions, S.max_clicks ) exclude WHERE ( imps > max_impressions OR convs >= max_conversions OR clicks > max_clicks ) ) ) valid, tgif.tbl_bannerimpressions I
where valid.bannerid=I.bannerid(+) and I.userid(+)=1 group by valid.bannerid, valid.totalweight — I want to see banners I haven’t seen yet, sorted by highest weight, so we sort by number — of times that this user has seen this particular banner, then we sort by weight order by impressions, totalweight DESC ) V, tgif.tbl_banners B, tgif.tbl_sponsors S where B.bannerid=V.bannerid and B.sponsorid=S.sponsorid and S.inactive != 1 and s.sponsorid not in ( ) valid, tgif.tbl_bannerimpressions I where valid.bannerid=I.bannerid(+) and I.userid(+)=1 group by valid.bannerid, valid.totalweight — I want to see banners I haven’t seen yet, sorted by highest weight, so we sort by number — of times that this user has seen this particular banner, then we sort by weight order by impressions, totalweight DESC ) V, tgif.tbl_banners B, tgif.tbl_sponsors S where B.bannerid=V.bannerid and B.sponsorid=S.sponsorid and S.inactive != 1 and s.sponsorid not in ( — Check the user impression cap to make sure it hasn’t been passed by the user select s.sponsorid from tgif.tbl_banners b, tgif.tbl_sponsors s, tgif.TBL_BANNERIMPRESSIONS i where s.sponsorid = b.sponsorid and b.bannerid = i.bannerid and i.timestamp >= sysdate - nvl(user_impression_cap_days,100) and userid = 1 group by s.sponsorid having count(*) >= max(nvl(user_impression_cap,1000000000)) ) — Make sure the sponsor is still in the valid table. This table is updated hourly — and contains the sponsors that have not gone over their sponsor level frequencies for — impressions/conversions/clicks and s.sponsorid in (select sponsorid from tgif.tbl_active_sponsors) ) where ranking=1 —Order the banners by sponsor weight, which is handled by the ranking —order by S.weight order by impressions, weight desc
would you trust a junior with this query?
) where rownum <= 10;