Ndtyjky

I need to know how a phone call affects our customers

so after I give a customer a phone call I need to see if they logged in to their account within 1 day after, or within 7 days after or within 14 days after the call.

How do I even go about using the datediff in this case?

Assuming that;
- the table is named logininfo and
- that call_time and logged_in_time are stored according to YYYY-MM-DD HH:MM (or one of the supported formats as per Date And Time Functions)

Then I believe that the following will do what you want :-

WITH 

        CTE1 AS (

            SELECT customer_id, strftime('%Y%m%d',logged_in_time) - strftime('%Y%m%d',call_time) AS daysafter  

            FROM logininfo 

            WHERE (strftime('%Y%m%d',logged_in_time) - strftime('%Y%m%d',call_time)) > 0 -- ignore login the same day

                AND customer_id = 1 -- must be for this customer

                AND date(call_time) = date('2018-01-01') -- must be in relation to this call (if wanted)

        )



SELECT (SELECT customer_id FROM CTE1 ORDER BY customer_id),

    (SELECT count() FROM CTE1 WHERE daysafter > 0 AND daysafter <= 1) AS 'logged in 1 day after',

    (SELECT count() FROM CTE1 WHERE daysafter > 0 AND daysafter <= 7) AS 'logged in 7 days after',

    (SELECT count() FROM CTE1 WHERE daysafter > 0 AND daysafter <= 14) AS 'logged in 14 days after'

;

• You would apply the appropriate customer_id and call_time to the where clause.


• Your expected results don't appear to comply with so after I give a customer a phone call I need to see if they logged in to their account within 1 day after, or within 7 days after or within 14 days after the call. as within 7 days after would include the number of calls made within 1 day after and so on. If this isn;t the case it's just a matter of changing the last 3 WHERE clauses appropriately.

Assuming a table populated as :-

The above will result in :-

The following is the full testing script used :-

DROP TABLE IF EXISTS logininfo;

CREATE TABLE IF NOT EXISTS logininfo (customer_id INTEGER, call_time TEXT, logged_in_time TEXT);

INSERT INTO logininfo VALUES

    (1,'2018-01-01 11:30','2018-01-02 10:00'), 

    (1,'2018-01-01 11:30','2018-01-03 10:00'),

    (1,'2018-01-01 11:30','2018-01-04 10:00'),

    (1,'2018-01-01 11:30','2018-01-05 10:00'),

    (1,'2018-01-01 11:30','2018-01-06 10:00'),

    (1,'2018-01-01 11:30','2018-01-07 10:00'),

    (1,'2018-01-01 11:30','2018-01-08 10:00'),

    (1,'2018-01-01 11:30','2018-01-15 10:00'),

    (1,'2018-01-01 11:30','2018-01-16 10:00'),

    (1,'2018-01-01 11:30','2018-01-17 10:00'),



    (1,'2018-02-01 11:30','2018-02-14 10:00'),

    (1,'2018-02-01 11:30','2018-02-15 10:00'),

    (1,'2018-02-01 11:30','2018-02-16 10:00'),

    (1,'2018-02-01 11:30','2018-02-17 10:00'),

    (1,'2018-02-01 11:30','2018-02-18 10:00'),

    (1,'2018-02-01 11:30','2018-02-19 10:00'),



    (2,'2018-01-01 11:30','2018-01-02 10:00'),

    (2,'2018-01-01 11:30','2018-01-03 10:00'),

    (2,'2018-01-01 11:30','2018-01-04 10:00'),

    (2,'2018-01-01 11:30','2018-01-05 10:00'),

    (2,'2018-01-01 11:30','2018-01-15 10:00'),

    (2,'2018-01-01 11:30','2018-01-16 10:00'),

    (2,'2018-01-01 11:30','2018-01-17 10:00')

    ;



SELECT * FROM logininfo;



WITH 

        CTE1 AS (

            SELECT customer_id, strftime('%Y%m%d',logged_in_time) - strftime('%Y%m%d',call_time) AS daysafter  

            FROM logininfo 

            WHERE (strftime('%Y%m%d',logged_in_time) - strftime('%Y%m%d',call_time)) > 0 -- ignore login the same day

                AND customer_id = 1 -- must be for this customer

                AND date(call_time) = date('2018-01-01') -- must be in relation to this call (if wanted)

        )



SELECT (SELECT customer_id FROM CTE1 ORDER BY customer_id),

    (SELECT count() FROM CTE1 WHERE daysafter > 0 AND daysafter <= 1) AS 'logged in 1 day after',

    (SELECT count() FROM CTE1 WHERE daysafter > 0 AND daysafter <= 7) AS 'logged in 7 days after',

    (SELECT count() FROM CTE1 WHERE daysafter > 0 AND daysafter <= 14) AS 'logged in 14 days after'

;

Note this doesn't use datediff rather the date difference is determined in the query.

You can make use of Conditional Sums in SQL Aggregate methods

SELECT SUM(CASE WHEN time > 1 AND time <= 7 THEN 1 ELSE 0 END)   AS LoggedInAfter1day,

       SUM(CASE WHEN time > 7 AND time <= 14 THEN 1 ELSE 0 END ) AS LoggedInAfter7day,

       SUM(CASE WHEN time > 14 THEN 1 ELSE 0 END )               AS LoggedInAfter14day

FROM ( SELECT (logged_in_time - call_time) As time FROM customers ) AS c