Flexible search query

[Former Member]

Hi Experts , Could you please help me on below query as i didn't get result as expected .

Query :
No. of users with who have never placed an order

SELECT COUNT(*) FROM ({{ SELECT DISTINCT({u.pk}) FROM {User as u JOIN Order as o ON {u.pk} = {o.user} } WHERE {Order} is null }}) as x

Really appreciated for reply

[former_member1212025 · Accepted solution]

Hi Satish,

Please try this query instead:

 SELECT COUNT(*) FROM {User as u} WHERE NOT EXISTS ({{ SELECT {pk} from {Order as o} WHERE {o.user} = {u.pk} }})

[Former Member]

Hi All , Could anyone help or guide me on below query as i need to do this one :

No. of users who have not placed an order in 5 days ".

SELECT COUNT(*) FROM {User as u} WHERE {o.modifiedtime} < NOW() - INTERVAL 5 DAY ({{ SELECT {pk} from {Order as o} WHERE {o.user} = {u.pk} }}).

I tried this one above query but its not working fine as expected .

Really help if anyone able to guide on above query .......

[Former Member]

Hi Pablo, Thanks for your reply . Could you please guide me more one below query if possible :

"No. of users who have not placed an order in 5 days ".

SELECT COUNT(*) FROM {User as u} WHERE {o.modifiedtime} < NOW() - INTERVAL 5 DAY ({{ SELECT {pk} from {Order as o} WHERE {o.user} = {u.pk} }})