Newly queried NXDOMAINs

This query gives you the nxdomains that have been queried today and not during the past 7 days. The date-part for the past seven days has to be adapted depending on the current date. Manually kind of tedious but a small script could solve this issue.

select domainname, count(*) as n from dns.staging one where rcode = 3
and not exists
(select distinct domainname from dns.queries two where
(one.domainname = two.domainname and
((year = 2017 and month = 10 and day = 11) or
(year = 2017 and month = 10 and day = 10) or
(year = 2017 and month = 10 and day = 9) or
(year = 2017 and month = 10 and day = 8) or
(year = 2017 and month = 10 and day = 7) or
(year = 2017 and month = 10 and day = 6) or
(year = 2017 and month = 10 and day = 5))))
group by one.domainname
order by n desc

harburclub.ch 422
magrit-bornet.ch 337
0xfffd0xfffd0xfffd0x20x8b0xfffd0x2linde-kryotechnik.ch 176
p0x160xfffd0x20xfffd0xfffd0xfffd0x2linde-kryotechnik.ch 143