User:Hegdeaditya/sandbox

'''Android app data '''

7-day Actives:

select full_date, count(distinct(mobile)) as active_users from transguid, dimdate where date(updated + time '05:30') between full_date-6 and full_date and aggregatorid = 30000 and date(updated + time '5:30') between '2013-09-01' and date(sysdate-1) group by 1 order by 1;

30-day Actives:

select full_date, count(distinct(mobile)) as active_users from transguid, dimdate where date(updated + time '05:30') between full_date-29 and full_date and aggregatorid = 30000 and date(updated + time '5:30') between '2013-09-01' and date(sysdate-1) group by 1 order by 1;

Top 30-apps – the below query is for Sep 2013, We need to change the month in the query from Oct-Feb and share it.

select keyword, count(distinct(g.mobile)) from transguid g, transaction t where g.transactionuuid = t.uuid and date(updated + time '5:30') between '2013-09-01' and date(sysdate-1) and aggregatorid = 30000 group by 1 order by 2 desc limit 30;