--1) Data Source: Custom SQL Query

WITH od AS (
    SELECT ogr.order_id
    , ogr.group_id
    , ogr.reservation_booking_id AS booking_id
    , ogr.order_datetime_est AS order_ts
    , ogr.style
    , ogr.uid
    , ogr.order_type
    FROM rtrbi.order_group_reservation ogr
    WHERE ogr.order_status = 'payment_received'
        AND ogr.order_type != 'STORE_INVENTORY_REQUEST'
    ),

re AS (
    SELECT review.id AS review_id
    , od.uid
    , od.style
    , review.created AS review_ts
    , od.order_id
    , od.group_id
    , od.booking_id
    , od.order_ts
    , ROW_NUMBER() OVER (PARTITION BY review.id ORDER BY od.order_ts, review.created) AS rnk
    , review.styleName
    , review.eventType
    , review.caption
    , review.content
    , review.rating
    , review.fit
    , review.sizeWorn
    , review.status
    , review.photo_count
    FROM etl.review review
    INNER JOIN od
        ON review.userId = od.uid
        AND review.styleName = od.style
        AND review.created > od.order_ts),

qm AS (
    SELECT *
    FROM (
        SELECT *
        , RANK() OVER (PARTITION BY user_id ORDER BY queue_start DESC) AS rnk
        FROM etl.queue_membership qm
        WHERE qm.queue_start IS NOT NULL
    ) qm
    WHERE qm.rnk = 1),

mapped_review AS (
    SELECT re.uid
    , qm.queue_parent_id
    , re.review_id
    , re.style
    , DATE(re.review_ts) AS review_date
    , re.order_id
    , DATE(re.order_ts) AS order_date
    , re.status
    , re.photo_count
    FROM re
    LEFT JOIN qm
        ON qm.user_id = re.uid
    WHERE re.rnk = 1
    ORDER BY 5,2,1),

shipments AS (
    SELECT ogr.uid
    , ogr.order_id
    , ogr.group_id
    , ogr.order_type
    , ogr.reservation_booking_id AS booking_id
    , DATE(ogr.order_datetime_est) AS order_date
    , ogr.style
    , ogr.last_fulfillment_status
    , ogr.rf_end_date
    FROM rtrbi.order_group_reservation ogr
    WHERE ogr.order_type != 'STORE_INVENTORY_REQUEST'
        AND DATE(ogr.order_datetime_est) >= '2015-08-02')

SELECT ship.order_date
, ship.uid
, mr.uid as reviewer
, mr.queue_parent_id
, ship.order_id
, ship.rf_end_date
, ship.group_id
, ship.booking_id
, ship.style
, CASE WHEN ship.order_type = 'QUEUE' THEN 'Unlimited' ELSE 'Classic' END AS order_type
, ship.last_fulfillment_status
, mr.review_id
, mr.review_date
, mr.status
, mr.photo_count
, CASE WHEN mr.review_date - ship.rf_end_date <= 14 THEN 1 ELSE 0 END AS day_14_review
, CASE WHEN mr.review_date - ship.rf_end_date <= 28 THEN 1 ELSE 0 END AS day_28_review
FROM shipments ship
LEFT JOIN mapped_review mr
    ON ship.uid = mr.uid
        AND ship.style = mr.style
        AND ship.order_id = mr.order_id
WHERE CURRENT_DATE > ship.rf_end_date
ORDER BY ship.order_date
;

--2.) Data Source: Daily Review Counts:

select
    date(r.created) as date
  , count(distinct r.id) as num_reviews
  , count(distinct r.userId) as unique_reviewers
  , round(count(distinct r.id) / count(distinct r.userId), 2) as reviews_per_reviewer
  , round( avg(case when r.rating > 0 then r.rating/2 else null end), 2) as avg_star_rating
  , count(distinct r.hasPhoto) as moments
  , sum(coalesce(r.photo_count,0)) as moment_photos
  , sum(coalesce(r.comment_count,0)) as comments
from etl.review r
inner join analytics.users au
    on au.uid = r.userId
    and au.mail not like '%renttherunway.com'
where date(r.created) between date(sysdate()) - 30 and date(sysdate()) - 1
group by 1
order by 1 desc

--3.) Data Source: Events in Review
select distinct r.review_date, rb.expected_begin_date, rb.uid, rb.style, rb.psize, r.eventType, rb.sku, r.id as review_id,
	  			r.caption, r.content, r.fit, r.sizeWorn, re.canonical_event, re.event, re.formality,
						   p.combo_type, p.mongo_collection, p.designer, p.title,
						   p.avl_date, p.sub_type, p.season_code, p.embellishment,
						   p.length, p.sleeve, p.color, p.fabric_code,
						   p.stretch, p.body_types, p.product_url, p.type, p.formality as formality_score
from (select distinct date(created) as review_date, r.eventType, r.id,
				   r.caption, r.content, r.fit, r.sizeWorn,
				   r.stylename, r.userId
 			from etl.review as r
 			where content is not null) as r
left join (select distinct rb.expected_begin_date, rb.uid, pmi.style, pmi.psize, pmi.sku
  	  from analytics.products_iid as pmi
	  inner join etl.reservation_booking as rb
	  on pmi.sku = rb.sku
      where rb.primary_booking_id is null
        and pmi.sku not in ('CLEARANCE_','NMBRD10_10','NMBRD10_12','NMBRD10_14','NMBRD10_16','NMBRD11_10','NMBRD11_12','NMBRD12_10',
'NMBRD12_12','NMBRD12_14','NMBRD12_16','NMBRD13_10','NMBRD13_12','NMBRD14_10','NMBRD14_12','NMBRD14_14',
'NMBRD14_16','NMBRD15_10','NMBRD15_12','WGACAVCBRC')) as rb
on rb.uid = r.userId and
   rb.style = r.styleName and
   rb.expected_begin_date interpolate previous value r.review_date
inner join etl.review_events as re
on r.id = re.id
inner join analytics.products as p
on rb.style = p.style
where r.review_date is not null
  and rb.expected_begin_date >= '2012-01-01'
  and re.canonical_event is not null
  and p.type in ('A','D')
  and re.canonical_event != 'N'

--4.) Data Source: Review Aggregation

select
    ap.type
  , ap.nid
  , ap.style
  , ap.designer
  , ap.title
  , ap.avl_date
  , ap.on_site
  , round(coalesce(avg(case when r.rating > 0 then r.rating / 2 else null end),0),2) as average_star_rating
  , round(coalesce(avg(
          case when fit = 'Small' then 1
               when fit = 'True to Size' then 2
               when fit = 'Large' then 3
               else null
          end
          ),0),2) as average_fit_rating
  , count(distinct r.id) as number_of_reviews
  , coalesce(sum(r.photo_count),0) as number_of_photos
  , sum(case when r.eventType = 'Date' then 1 else 0 end) as 'Date'
  , sum(case when r.eventType = 'Party' then 1 else 0 end) as 'Party'
  , sum(case when r.eventType = 'Wedding' then 1 else 0 end) as 'Wedding'
  , sum(case when r.eventType = 'Vacation' then 1 else 0 end) as 'Vacation'
  , sum(case when r.eventType = 'Formal Affair' then 1 else 0 end) as 'Formal_Affair'
  , sum(case when r.eventType = 'Other' then 1 else 0 end) as 'Other'
from analytics.products ap
left join etl.review r
    on r.styleName = ap.style
group by 1,2,3,4,5,6,7

--5.) Data Source: Yesterday's Reviews

select
    r.styleName
  , ap.nid
  , ap.title
  , date(r.created) as date
  , au.mail
  , round(r.rating/2) as rating
  , eventType as occassion
  , r.caption as review_title
  , r.content as review
  , coalesce(r.photo_count,0) as moment_photo_count
  , coalesce(r.comment_count,0) as comments
from etl.review r
inner join analytics.users au
    on au.uid = r.userId
    and au.mail not like '%renttherunway.com'
inner join analytics.products ap
    on ap.style = r.styleName
where date(r.created) between
        case when dayofweek(sysdate()) = 2 then date(sysdate()) - 3 else date(sysdate())-1 end
    and date(sysdate())- 1
order by r.rating, r.styleName