How to combine two queries with case statement

How to combine two queries with case statement

query1

SELECT   DATE(created_at),COUNT(DISTINCT user_id) AS count1 
FROM     user
WHERE    by_user = '3'  
AND      DATE(created_at) BETWEEN CURRENT_DATE-2 AND CURRENT_DATE
GROUP BY DATE(created_at) 

query2

SELECT   DATE(created_at),COUNT(DISTINCT user_id) AS count2 
FROM     USER  
WHERE    by_user <> '3'  
AND      DATE(created_at) BETWEEN CURRENT_DATE-2 AND CURRENT_DATE
GROUP BY DATE(created_at) 

Expected output:

+---------------------+--------+--------+
| date(created_at)    | count1 | count2 |
+---------------------+--------+--------+
| 30/12/2019,12:00 AM | 4000   | 5000   |
| 29/12/2019,12:00 AM | 2434   | 5432   |
+---------------------+--------+--------+

Answer

With conditional aggregation:

select date(created_at), 
  count(distinct case when by_user = '3' then user_id end) as count1, 
  count(distinct case when by_user <> '3' then user_id end) as count2 
from user  
where date(created_at) between current_date-2 and current_date
group by date(created_at) 

The conditions where by_user = '3' or where by_user <> '3' are checked with a CASE expression, so they are removed from the WHERE clause.