SQL query to get number of clients with last statement equal connected

I need to make a SQL query

table ‘records’ structure:

contact_id(integer), 
client_id(integer), 
worker_id(integer), 
statement_status(varchar), 
contact_ts(timestamp)

It has to show the following:

current date
number of clients which last statement_status was 'interested'
number of clients which last statement_status was 'not_interested' and previus status was 'not_present'

Could somebody help?

sample data:

contact_id  client_id   contact_ts                worker_id statement_status
'1',    '181',      '2017-09-24 03:38:31.000000', '107',    'voicemail'
'2',    '72',       '2017-09-23 09:32:38.000000', '10',     'not_interested'
'3',    '277',      '2017-09-22 07:06:16.000000', '119',    'interested'
'4',    '36',       '2017-09-21 04:39:57.000000', '118',    'not_present'
'5',    '33',       '2017-09-20 04:12:12.000000', '161',    'voicemail'
'6',    '244',      '2017-09-19 02:26:30.000000', '13',     'not_interested'
'7',    '346',      '2017-09-18 02:30:35.000000', '255',    'interested'
'8',    '128',      '2017-09-17 06:20:13.000000', '52',     'not_present'
'9',    '33',       '2017-09-16 08:58:02.000000', '188',    'not_present'
'10',   '352',      '2017-09-15 08:18:40.000000', '324',    'not_interested'
'11',   '334',      '2017-09-14 04:27:40.000000', '373',    'interested'
'12',   '2',        '2017-09-13 08:44:40.000000', '40',     'not_present'
'13',   '33',       '2017-09-12 03:46:16.000000', '252',    'voicemail'
'14',   '366',      '2017-09-11 04:31:22.000000', '78',     'not_interested'
'15',   '184',      '2017-09-10 06:08:01.000000', '289',    'interested'
'16',   '184',      '2017-09-09 05:45:56.000000', '124',    'not_present'
'17',   '102',      '2017-09-08 07:09:30.000000', '215',    'voicemail'
'18',   '140',      '2017-09-07 08:09:18.000000', '196',    'not_interested'
'19',   '315',      '2017-09-06 05:13:40.000000', '242',    'interested'
'20',   '268',      '2017-09-05 07:41:40.000000', '351',    'not_present'
'21',   '89',       '2017-09-04 05:32:05.000000', '232',    'voicemail'

desired output:

Time,                   interested, not-interested
2017-09-10 06:08:01,    5,          5

I tried something with sub queries, but it obviously doesn’t work:

SELECT 
GETDATE()
,(select count(*) 
            from record a 
            where (select statement_status 
                   from record
                   where client_id == a.client_id
                   order by a.contact_ts
                   limit 1) == "interested"
                   group by a.contact_id)
            
,(select count(*) 
            from record a 
            where (select (select statement_status 
                   from record
                   where client_id == a.client_id
                   order by a.contact_ts
                   limit 2) order by a.contact_ts desc limit 1) == "interested"
                   and
                   (select statement_status 
                   from record
                   where client_id == a.client_id
                   order by a.contact_ts
                   limit 1) == "interested"
                   
                   group by a.contact_id)
            
from record b;

How should I use the inner selects?
I must write a poem, because most of my post is a code. So maybe something from “Dead man”?

“Don’t let the sun burn a hole in your ass, William Blake. Rise now, and drive your cart and plough over the bones of the dead!”

😉

Answer

Try something like this:

WITH status AS (
  SELECT DISTINCT client_id, 
    first_value(statement_status) OVER w1 AS last_status,
    nth_value(statement_status, 2) OVER w1 AS prev_status
  FROM records 
  WINDOW w1 AS (PARTITION BY client_id ORDER BY contact_ts DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
)
SELECT CURRENT_DATE(),
  SUM(last_status = 'interested') AS interesed, 
  SUM(last_status = 'not_interested' AND prev_status = 'not_present') AS not_interested
FROM status

Advertisement

Answer