import os
import pandas_redshift as pr
import matplotlib
matplotlib.rcParams["figure.figsize"] = [12, 12]
pr.connect_to_redshift(dbname = os.environ["PG_DBNAME"],
host = os.environ["PG_HOST"],
port = 5432,
user = os.environ["PG_USER"],
password = os.environ["PG_PASS"]
)
df = pr.redshift_to_pandas(f"""
select domains.id, domains.name, m.next_expiration_at as min_after, h.next_expiration_at as h_after, d.next_expiration_at as d3_after
from scan_results m
join scan_results h on m.domain_id = h.domain_id and h.stage = 'hour_after_exp' and h.type='ssl'
join scan_results d on m.domain_id = d.domain_id and d.stage = '3days_after_exp' and h.type='ssl'
join domains on domains.id = m.domain_id
where m.type='ssl' and m.next_expiration_at between '2020-11-01' and '2020-11-20' and m.stage = 'minute_before_exp' and d.next_expiration_at > m.next_expiration_at
""")
df['id'].plot.hist()
