Number of sales in a specific timeframe

Question

I am trying to answer the following question with the following table - Number of visitors who visited the website in July 2021?

● Table name: VISITOR_TABLE

• VISITOR_ID → unique ID representing a visitor
• VISIT_ID → unique ID representing a visit
• VISIT_DATETIME → timestamp representing when the visit happened ○ DEMO_VIDEO_WATCHES → yes/no flag representing whether the visitor watched the demo video

my code is as follows:

SELECT SUM( DISTINCT VISITOR_ID)
FROM VISITOR_TABLE 
WHERE VISIT_DATETIME IN
( SELECT TIMESTAMP("2021-07"
FROM VISITOR_TABLE  );

Is there anything I am not considering enough to answer this question?

Answer 1

Use a range condition:

SELECT count(DISTINCT visitor_id)
FROM visitor_table 
WHERE visit_datetime >= date '2021-07-01'
  and visit_datetime < date '2021-08-01'
FROM visitor_table

To get the number of visits you need to use count() not the sum of the values.

Answer 2

You can use timestamps (of beginning and end of the month) to limit a query only to exaxct month

SELECT COUNT( DISTINCT VISITOR_ID)
FROM VISITOR_TABLE 
WHERE 
VISIT_DATETIME BETWEEN 
TIMESTAMP '2021-07-01' AND 
TIMESTAMP '2021-07-01' + interval '1 MONTH - 1 MICROSECOND'

This query should be OK if timestamp is without timezone.