import sqlite3,bisect,statistics as S
d=sqlite3.connect('/opt/ppcopy/live_paths.db')
P=d.execute("select pool,venue,quote,t_first,cap_first,max_cap from pools").fetchall()
c1=sorted(r[0] for r in d.execute("select t from cross_t where thr=1000000"))
def conc(t,w): return bisect.bisect_right(c1,t)-bisect.bisect_left(c1,t-w)
for W in (3600,6*3600):
  rows=[(conc(p[3],W),p[5]>=1e6,p[3]) for p in P if p[4] and p[4]<100000 and (p[1]=='v3' or p[1].startswith('v4'))]
  rows.sort(key=lambda r:r[2]); n=len(rows); print('W',W,'n',n,'base',sum(r[1] for r in rows)/n)
  for name,sub in (('H1',rows[:n//2]),('H2',rows[n//2:])):
    sub2=sorted(sub,key=lambda r:r[0]); k=len(sub2)//5; out=[]
    for q in range(5):
      s=sub2[q*k:(q+1)*k if q<4 else None]; out.append((s[0][0],s[-1][0],round(sum(x[1] for x in s)/len(s)*100,2)))
    print(name,out)