addbacks
I think it would be safer/cleaner to re-compute daily visibility and then group by / count per country, day, (optionally taxo). Would also be nice to read this all in SQL CTEs. I don't trust myself to verify the logic on pre-computed tables, moving from SQL to python, etc..., but if someone else feels they verify that's okay with me. :-)
WHERE
Whey didn't you apply these filters from the above query again here? * AND sourcetype = 'Employer' * AND feedid = 50461
AND sj.TS::DATE = DATEADD('day', -1, m.first_match_date)
This only counts for a given day the number of jobs newly removed that day. But don't we want to add it back every day it should not have been removed?
the rule changes the job’s visibility level from organic/jobalert to sponsored
Same edge case as above, but does it explicitly have to change the visibility or does it just ensure that visibility on days 31+ is sponsored?
before the rule fired
Weird edge case related to my above comment, but what if the job was not visible on days <30 for a different reason, then it became eligible for visibility on day 31 but incurred rule 244435 and so it stayed invisible days 31+. We would want to add this back, right?
LEFT JOIN other_rules ON fm.jobid = other_rules.jobid
I think this should be joined on jobid + day, since it's possible those rules applied on day(s) <30. For example if job 123456 has this time series:
... then we would not add this job back, because we just join on distinct jobid. We should consider only cases where the other rules applied after day 30. I think the most robust way to do this is to add back per day. We can work on an alternative query to see how different the results are.
first_match
Would be nice to confirm that jobs don't return to organic or jobalert visibility for any reason after this first_match date.