Querying over two different time periods in NRQL (now and the past)

Hello

We have an estate of Linux instances that have automated weekly maintenance windows at various times and days across the week. If a maintenance period requires a server to reboot (e.g. if a new kernel has been installed) a flex agent sets the RebootRequired field for that EntityId in RequiresRebootSample to a non-zero value. This is (for reasons) updated every 30 seconds.

I want to provide our application developers with a dashboard that shows only servers that currently (i.e. in the last hour) have a non-zero RebootRequired, and the number of days it has been non-zero, so that they can check daily to see how many instances are going to be rebooted in the coming days, and take appropriate action.

I am struggling to write NRQL that can query based on the results of another query… some kind of SQL/NRQL hybrid would look like this:

SELECT round(filter(count(entityName), where rebootRequired>0/2880)) as DaysRequiringReboot from RequiresRebootSample where entityName in (select uniques(entityName) from RequiresRebootSample where rebootRequired>0 since 1 hour ago limit max) since 7 days ago limit max facet entityName

Is such a thing possible?

Thanks in advance!