Interesting or fun queries / reports?

[GeekNJ]

I'm a newer user and one of the items I was looking to see is the first detection for each species, as I didn't notice one available. With the Tools / Database Maintenance / SQL Command page, we have the ability to query the database used on the pi. I created the below that shows the first date each species was identified. Anyone else have any interesting queries?

select Com_Name as 'Common Name', min(Date) as 'First Detection'
from detections
group by Com_Name
order by min(Date) desc;

[Phaeton]

I find the swings interesting with which birds are detected. Birds that are once high hitters disappear, possibly migrating out, and are replaced by something else. Below shows birds, sorted by number of detections, when they were last detected, and days since they were last detected.

SELECT Com_Name AS 'Common Name', COUNT(Date) AS 'Detections', MAX(Date) AS 'Last Detection',
CAST(JULIANDAY('now', 'localtime') - JULIANDAY(MAX(Date)) AS INT)  AS 'Days Since Last Detection'  
FROM detections
GROUP BY Com_Name 
ORDER BY Detections DESC