Condition based summarization

[Vikas038]

Hi Team, With In-DB tools, below is my requirement

* I need readership in first two days for a research paper available on our website.

* For that i am putting conditions in formula. Because summarization tool doesn't allow me to place any condition for aggregate function

Since i have to deliver result with my management. So please provide solutions as soon as possible. 

For your reference, i am attaching screen shot for the same.

[OllieClarke]

Hi @Vikas038, I don't currently have access to a database, so I've made a workflow without in-db tools. The logic should transfer to in-db though. 

I've made a dataset with 3 columns: a paperid, a paper's published datetime, and the datetime it was read. I then created an additional boolean field which shows if a read is within 2 days of a paper being published. You can then group by this and the paper in your summarise, count the number of reads and then filter out those which weren't within 2 days.

[Vikas038]

Thanks for quick reply Clarke.

Although, if you look at the screen shot that i shared. i have to apply case conditions while taking the time diff. between publication data and usage date. Like if paper published on Friday then it should considered next four days. because meanwhile weekend is falling(sat,sun)

[OllieClarke]

Hi @Vikas038 in that case I would change the approach slightly. Here I've calculated the 'cut-off' based on the day of the week the paper was published. This is similar to your formula. Then you can create the boolean that says if a read date is before or equal to this cut-off, and then it's the same onward. (i've made the assumption that a paper would never be published on the weekend)

[Vikas038]

Hi Olie,

It will give me another column with value 2 and then i can use it in summarization.

Although, my motto is to apply case statement so that will have column name 'UniqueReads_2Days' and count of unique users in cells 

i am trying hard to resolve below script in athena, it willl be highly appreciated if you let me know that where i am doing mistake in below code

select R.doc_id
,count(distinct(case when R.dateofconsumption between date_add('day',-1,cast(substr(publicationdate,1,10) as date))
and (case when extract(dow,cast(substr(publicationdate,1,10) as date))=5 then date_add('day',4,cast(substr(publicationdate,1,10) as date))
when extract(dow,cast(substr(publicationdate,1,10) as date))=4 then date_add('day',4,cast(substr(publicationdate,1,10) as date))
when extract(dow,cast(substr(publicationdate,1,10) as date))=6 then date_add('day',4,cast(substr(publicationdate,1,10) as date))
when extract(dow,cast(substr(publicationdate,1,10) as date))=7 then date_add('day',3,cast(substr(publicationdate,1,10) as date) else date_add('day',2, cast(substr(publicationdate,1,10) as date)) end) then R.userid end)) as UniqueDocReads48hr
,count(distinct(case when R.dateofconsumption between date_add('day',-1, cast(substr(publicationdate,1,10) as date))
and (case when extract(dow,cast(substr(publicationdate,1,10) as date))=1 then date_add('day',19,cast(substr(publicationdate,1,10) as date)) else date_add('day',21,cast(substr(publicationdate,1,10) as date)) end) then R.userid end)) as UniqueDocReads15Days

from view_mdc_ma_research R,view_dim_mongo_researchcombined C,view_mac_ma_userlookup U
where upper(R.userid)=upper(U.userid) and R.doc_id=C.doc_id
and usersource not in ('MAC')
and lower(email) not like '%moodys%'
and dateofconsumption between timestamp'2018-08-11' and timestamp'2019-10-09'
and cast(substr(publicationdate,1,10) as date) between timestamp'2018-08-11' and timestamp'2019-09-26'
and lower(documenttype) not in ('case study',
'market comment',
'market outlook',
'market signals review',
'moody''s analytics methodology',
'sector insights',
'viewpoints',
'global risk perspectives',
'international policy perspectives',
'prã©cis state',
'précis state',
'risk monitor',
'market data',
'announcement',
'announcement servicer',
'rating action',
'rating action servicer',
'announcement - servicer',
'rating action - servicer',
'announcement - translation',
'rating action - translation',
'mir emg markets',
'mir mcs','mir cds',
'market outlook-data supplement')
group by R.doc_id