I am working on retrieving data that has to meet to conditions without having an actual dataset -

• #Condition 1: Watched demo in 2021
• #Condition 2: After watching the demo, placed an order within 2 days

Table name: visitor

1. VISITOR_ID → unique ID representing a visitor
2. VISIT_ID → unique ID representing a visit
3. VISIT_DATETIME → timestamp representing when the visit happened
4. DEMO_VIDEO_WATCHES → yes/no flag representing whether the visitor watched the demo video

Table name: ORDERS

1. ORDER_ID → unique ID representing an eCommerce order
2. VISITOR_ID → unique ID representing the Google Analytics visitor
3. ORDER_DATETIME → timestamp representing when the order happened
4. ORDER_ARR → ARR (annual recurring revenue) from the order placed

The question I am trying to answer is - Amount of ARR associated with visitors who watched the demo video in the 2021 calendar year and then placed an order within 2 days of watching the demo video.

My answer is -

SELECT SUM (ORDER_ARR) AS Amount_of_ARR
FROM
(SELECT *
FROM ORDERS 
WHERE DEMO_VIDEO_WATCHES = yes AND year(VISIT_DATETIME)='2021') t
INNER JOIN visitor v
ON t.VISITOR_ID = v.VISITOR_ID
WHERE t.ORDER_DATETIME BETWEEN NOW() - INTERVAL '2 DAYS'AND NOW()

However the system says it's invalid, what did I do wrong?

CodePudding user response：

you want the t.ORDER_DATETIME FROM last 2 days from now?

try:

where t.ORDER_DATETIME >= dateadd(day, -2, getdate())

you can play with the dateadd(day, -2, parameter) to get the data from the 2 days from the parameter

CodePudding user response：

where t.ORDER_DATETIME BETWEEN VISIT_DATETIME AND VISIT_DATETIME   2