Two WHERE with two independent conditionals

Asked

Viewed 60 times

0

Fala Pessoal, I am mounting a query with the objective of bringing 2 columns that do the same query but with 2 different conditions. ex: 

SELECT 
         COUNT(MAX(l.created_at))as last_login_x1,
         COUNT(MAX(l1.created_at)) as last_login_x2
      FROM usuarios as u
           INNER JOIN register as r ON r.id = u.user_id
           LEFT JOIN last_login as l ON l.user_id = u.user_id
     LEFT JOIN last_login as l1 ON l1_id = u.user_id
      WHERE u.status = 'approved'
       AND l1.amount  > 1
       AND l.amount > 1 
     GROUP BY u.user_id
    HAVING MAX(l1.created_at) < DATE(CURRENT_DATE) - interval '30 days'
AND MAX(l.created_at) >= DATE(CURRENT_DATE) - interval '30 days'

The idea is to tell users that did not make login in the last 30 days versus logged in in the last 30 days

mysql sql postgresql

  • You can add the DDL of your database, so that a query example is provided?

  • One possibility is to reference only the last_login table and in the select fields use one SUM(CASE MAX(l.created_at) < DATE(CURRENT_DATE) - interval '30 days') THEN 1 ELSE 0) AS last_login_x1. Idem for last_login_x2.

1 answer

0

Good morning

One way to eliminate scanning in a table would be to reuse the data for both situations. In this case, it is possible to apply.

SELECT
    SUM(CASE WHEN l.created_at >= DATE(CURRENT_DATE) - interval '30 days' THEN 1 ELSE 0 END) AS last_login_x1,
    SUM(CASE WHEN l.created_at < DATE(CURRENT_DATE) - interval '30 days' THEN 1 ELSE 0 END) AS last_login_x2
FROM usuarios AS U
INNER JOIN register AS R 
    ON r.id = u.user_id
LEFT JOIN (SELECT user_id,MAX(created_at) AS created_at FROM last_login WHERE amount > 1 GROUP BY created_at) as l 
    ON l.user_id = u.user_id
WHERE 
    u.status = 'approved'

Anything, just call, I think will solve your problem.

Browser other questions tagged mysql sql postgresql

You are not signed in. Login or sign up in order to post.