SQLite code to extract Pi-Hole DBs and mine your DNS queries

[FunkyBuddha]

attach 'pihole-FTL.db' as db1;
attach 'gravity.db' as db2;
SELECT distinct strftime('%m-%d-%Y', datetime(timestamp, 'unixepoch')) as datestamp
    ,datetime(timestamp, 'unixepoch') as tymestamp
    ,timestamp
    ,d.domain
    ,c.ip
    ,c.name
    ,e.hwaddr
    ,e.macVendor
    ,e.numQueries
    ,strftime('%m-%d-%Y', datetime(firstSeen, 'unixepoch')) as firstSeen
    ,strftime('%m-%d-%Y', datetime(lastQuery, 'unixepoch')) as lastQuery
    ,case
       when f.domain is NULL then 'N'
       else 'Y'
    end as blocked
    ,case
       when f.domain is NULL then NULL
       else f.adlist_id
    end as adlist_id
FROM db1.query_storage q
join
db1.domain_by_id d
on d.id = q.domain
left join
db1.client_by_id c 
on c.id = q.client
left join
db1.network_addresses d
on c.ip=d.ip
left join
db1.network e 
on d.network_id=e.id
left join
(select domain, group_concat(adlist_id) as adlist_id from db2.gravity group by 1) f 
on d.domain=f.domain
;