Subquery in NRQL to filter based on different time period data

I’m trying to write up a query which retrieves the newly(in past 24 hours) reported errors when compared to last week. I tried to make this via nested SELECT query like below, but it doesn’t seem to be supported in NRQL. Any alternatives or suggestions on how this could be tackled?
Here is what my approach looks like,
// BREAKDOWN
// GET all unique errors reported since yesterday
SELECT uniques(error.message) FROM TransactionError WHERE appName = 'xyz' AND error.message IS NOT NULL SINCE 1 day ago

// GET all unique error.class reported in last week
SELECT uniques(error.message) FROM TransactionError WHERE appName = 'xyz' AND error.message IS NOT NULL SINCE last week UNTIL 1 day ago

//COMBINING BOTH QUERIES ABOVE,
SELECT uniques(error.message) FROM TransactionError WHERE appName = 'xyz' AND error.message IS NOT NULL AND error.message NOT IN (SELECT uniques(error.message) FROM TransactionError WHERE appName = 'xyz' AND error.message IS NOT NULL SINCE last week UNTIL 1 day ago) SINCE 1 day ago

Tried to use NOT IN with subquery, but NRQL QueryBuilder throws error “This query has NRQL Syntax Error: Error at line 1 position 132, unexpected ‘SELECT’”

I did give a try using COMPARE WITH which came closer to my need,

SELECT count(*), uniqueCount(error.message) FROM TransactionError WHERE appName = 'xyz' AND error.message IS NOT NULL SINCE 1 week ago COMPARE WITH 1 day ago LIMIT MAX

Hi @jesubram - Unfortunately you cannot perform a select to use for an IN filter for nested queries.

@JoiConverse - This would be a great feature request as I have seen similar posts using a nested query to filter data with the IN clause.

1 Like

@stefan_garnham Thanks for this feature idea suggestion. I’ll see if there is something in progress around this already and submit if not.