Number of sales in a specific timeframe-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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.