CMS DB: Publisher payment data dump

[buddymanga]

we need the following data dump:

Series Name,

[buddymanga]

We will need following data in below format.

• Publisher ID – Unique ID for each publishers
• Series ID – Unique ID for each series
• Monthly Pageview Data per series

Sample: Publisher ID Series ID Month Page View (%) A0001 TA0001 Jan 2.50% N0002 TN0002 Jan 3.50% C0003 TC0003 Jan 4.50%

[varmab]

It will be Monthly Report Page View will be calculated as

Total number of Page Views for Specific Series for Month / (Total page views of all Series of All Publishers)

buddy: For the last 3 months

[varmab]

I emailed all data dump files and query

--Totals select P.name as Publisher,S.name as Series,count(U.eventtype) as TotalCount,round((count(U.eventtype)/ (select count(U.eventtype) as TotalCount from public.userevent U where eventtype='page_opened' AND EXTRACT(MONTH FROM U.timestamp)=11 AND EXTRACT(YEAR FROM U.timestamp)=2019 )::numeric) *100,2) as Percentage from public.userevent U inner join public.chapter C ON C.id=U.chapterid inner join public.series S ON S.id=C.seriesid inner join public.publishertoseries PS ON ps.seriesid=S.id inner join public.publisher P ON p.id=ps.publisherid where eventtype='page_opened'AND EXTRACT(MONTH FROM U.timestamp)=11 AND EXTRACT(YEAR FROM U.timestamp)=2019 GROUP BY eventtype,P.name,S.name ORDER BY Percentage DESC