import sqlalchemy as sal
from sqlalchemy import create_engine
import pandas as pd
from credential import usrnm, pwd, host, db
from datetime import timedelta
import time
start = time.time()
engine = create_engine('postgresql://'+usrnm('vn')+':'+pwd('vn')+'@'+host()+':5432/'+db('vn'))
conn = engine.connect()
command_u = """with presum as(
select dbg_add."userID",
dbg_add."chargeDate",
date_trunc('day',user_profile."firstLoginDate") as "firstLoginDate",
case
when dbg_add."chargeDate" = date_trunc('day',user_profile."firstLoginDate") then dbg_add."grossRev"
else 0
end as "GrossRev00",
case
when dbg_add."chargeDate" <= date_trunc('day',user_profile."firstLoginDate") + interval '1 day' then dbg_add."grossRev"
else 0
end as "GrossRev01",
case
when dbg_add."chargeDate" <= date_trunc('day',user_profile."firstLoginDate") + interval '3 days' then dbg_add."grossRev"
else 0
end as "GrossRev03",
case
when dbg_add."chargeDate" <= date_trunc('day',user_profile."firstLoginDate") + interval '7 days' then dbg_add."grossRev"
else 0
end as "GrossRev07",
case
import sqlalchemy as sal
from sqlalchemy import create_engine
from credential import usrnm, pwd, host, db
import pandas as pd
from datetime import timedelta
import time
start = time.time()
engine = create_engine('postgresql://' + usrnm('th') + ':' + pwd('th') + '@' +
host() + ':5432/' + db('th'))
conn = engine.connect()
command = """select date_trunc('day',"firstLoginDate") as "1stLog",
A."activeDate",
count(*)
from user_profile
inner join user_active_events A on A."userID" = user_profile."userID"
where date_trunc('day',"firstLoginDate") >='2020-01-01'
group by 1,2
"""
dfu = pd.DataFrame(engine.execute(command),
columns=['1stLogin', 'activeDate', 'activeUsers'])
dfu['activeDate'] = pd.DatetimeIndex(dfu['activeDate'])
command_nru = """select count(*) as "NRU",
date_trunc('day', "firstLoginDate") as "1stLogin"
from user_profile
where date_trunc('day', "firstLoginDate") >= '2020-01-01'
group by 2"""
nru = pd.DataFrame(engine.execute(command_nru),
columns=['NRU', '1stLogin']).sort_values(by=['1stLogin'],
ascending=[True])
conn.close()
import sqlalchemy as sal
from sqlalchemy import create_engine
import pandas as pd
from credential import usrnm, pwd, host, db
from date_mod import date_part, date_trunc
engine = sal.create_engine('postgresql://' + usrnm('vn') + ':' + pwd('vn') +
'@' + host() + ':5432/' + db('vn'))
conn = engine.connect()
command = """select count("userID"), "activeDate" from user_active_events group by 2 """
dau = pd.DataFrame(engine.execute(command), columns=['DAU', 'activeDate'])
dau["activeDate"] = pd.DatetimeIndex(dau["activeDate"])
print("Daily active users (per day):")
print(dau)
avg_dau = dau
avg_dau["Month"] = avg_dau["activeDate"].apply(date_trunc, args=('month', ))
avg_dau = avg_dau.groupby(["Month"]).sum().reset_index()
avg_dau["DAU"] = avg_dau["DAU"] / avg_dau["Month"].apply(date_part,
args=('eom', ))
print("Average daily active users (per month):")
print(avg_dau)
conn.close()