Question: Query: - - Component View Data DROP TABLE IF EXISTS SCRATCHDB.search _ rag; CREATE TABLE SCRATCHDB.search _ rag as ( select chegg _ uuid as
Query:
Component View Data
DROP TABLE IF EXISTS SCRATCHDB.searchrag;
CREATE TABLE SCRATCHDB.searchrag
as
select
chegguuid as useruuid,
contentcontentid as questionuuid,
contentmetadatamultiturnchatconversationid as conversationid
contentmetadatasearchstrackid as strackid,
contentmetadatamultiturnchatmessageid as messageid
contentmetadatacsqasolutionids as answeridcontrol group
contentmetadatacsqasolutionuuids as originalansweruuid, treatment group
contentmetadatacsqaisragsolution as ragflag,
contentmetadatasearchresultmatchbadge as matchbadge
from edw.riodbenrichedprod.clickstreamcomponentview
where year
and mon in
and eventtime between and CURRENTDATE
and viewviewversion like mtc
and geocountrycode US
and componentviewcomponentname 'answer'
Interaction Data
DROP TABLE IF EXISTS SCRATCHDB.interactionrag;
CREATE TABLE SCRATCHDB.interactionrag
as
select
cicontentmetadatasearchstrackid as strackid
cichegguuid,
cieventtime,
ciinteractionelementvalue
from
edw.riodbstructuredprod.clickstreaminteraction ci
where
ciyear in
and cimon
and ciday
and civiewviewversion mtc
and ciinteractionelementname in rating'ratings','submit rating'
and cibaseeventtype'web'
and cichegguuid in select distinct useruuid from scratchdb.searchrag where ragflag true
and cicontentmetadatasearchstrackid is not null
Allocation Data
DROP TABLE IF EXISTS SCRATCHDB.eligibleusers;
CREATE TABLE SCRATCHDB.eligibleusers
as
Select
oachegguuid as uuid,
case when oavariantid then 'Control'
when oavariantid then 'Test' end as ExpGroup
From
edw.riodbenrichedprod.optimizelyallocation oa
Where
oayear
and oaexperimentid
and oavariantid in
and oachegguuid is not null
Control:
Select
bqid as questionid
CountDistinct rev.id as totalratings,
CountDistinct case when rev.reviewvalue then rev.id end as Positiveratings,
CountDistinct case when rev.reviewvalue then rev.id endCountDistinct rev.id as CFScore
From
Odsdb.reviewuserentityreview rev
Join SCRATCHDB.eligibleusers eu on rev.useruuid euuuid
join odsdb.boardquestion bq on bqid rev.questionid
join SCRATCHDB.searchrag s on suseruuid euuuid and squestionuuid rev.questionuuid
Where
rev.entitytype
and rev.reviewtype
and rev.createdtime
and euExpGroup 'Control'
and smatchbadge in exact'EXACTMATCH'
Group by ;
Treatment:
with maxevent as
select strackid chegguuid, maxeventtime as eventTime
from scratchdb.interactiondatarag
group by
Select
bqid as questionid
CountDistinct iinteractionelementvalue as totalratings,
CountDistinct case when iinteractionelementvalue in 'positive','Positive' then iinteractionelementvalue end as Positiveratings,
CountDistinct case when iinteractionelementvalue in 'positive','Positive' then ichegguuid endCountDistinct iinteractionelementvalue as CFScore
From
SCRATCHDB.interactionrag i
join SCRATCHDB.searchrag s on suseruuid ichegguuid
join odsdb.boardanswer ba on bauuid soriginalansweruuid
join odsdb.boardquestion bq on bqid baquestionid
join SCRATCHDB.eligibleusers e on euuid ichegguuid
join maxevent m on mstrackid istrackid and mchegguuid ichegguuid and meventTime ieventtime
Where
ieventtime
and sragflag true
and eexpgroup 'Test'
Group by ;
Step by Step Solution
There are 3 Steps involved in it
1 Expert Approved Answer
Step: 1 Unlock
Question Has Been Solved by an Expert!
Get step-by-step solutions from verified subject matter experts
Step: 2 Unlock
Step: 3 Unlock
Study Help