SQLPad
Write a query to return the total number of users who have searched on new year's day: 2021-01-01.
Table: search
col_name| col_type ----------+------------ country | varchar(2) date | date user_id | integer search_id | integer query | text
Sample results
num_searches -------------------- 1234567899
Expected results
Solution 1: postgres
SELECT COUNT(DISTINCT user_id)
FROM search
WHERE date = '2021-01-01';
Explanation
This query is asking the database to count the number of distinct (unique) user IDs that appear in the "search" table for the date January 1st, 2021. In simpler terms, it's trying to find out how many different users searched something on the website on New Year's Day.
Solution 2: postgres
SELECT COUNT(user_id) FROM (
SELECT
user_id
FROM search
WHERE date = '2021-01-01'
GROUP BY 1
) X;
Explanation
This query is counting the number of unique users who performed a search on January 1st, 2021.
The inner query selects the user_id column from the search table where the date is January 1st, 2021 and groups the results by user_id. This creates a list of unique user_ids who performed a search on that date.
The outer query then counts the number of user_ids in that list and returns the result.