CODEWITHSUNDEEP
sqlpostgresql
João Reberti
João Reberti

Reputation: 25

Postgres - Retrieve inner join oldest record

So at my job management wants to assess how long does it take for us to contact a lead. How many leads were contacted before 1 hour, between 1 and 3 hours, and so on.

With this query I get close to the result, but if said lead was contacted more than once, which is the most common occurrence, the results become skewed, for example: - Lead enters ad 9:00, gets contacted at 9:30, counts one for column "Before 1 hour", then it gets contacted subsequently N times, so it the event will count in all different columns. All I want is that FIRST contact.

Is there way for the INNER JOIN to only account for the oldest hence first contact, the information relevant for us?

ws_ts is the lead timestamp and call_start is self explanatory. The leads table and call_logs join on contact_id.

Thanks in advance. My select:

    select 
    min(dcl.call_start::date) as Date, 
    sum(
        case
            when (((dcl.call_start) - (lsl.ws_ts))::interval) < '01:00:00'
            then 1
            else 0 
                end

    )as "Lead called before 1 hour",

    sum(
        case
            when ((  ((dcl.call_start) - (lsl.ws_ts)))::interval) > '01:00:00' and ((  ((dcl.call_start) - (lsl.ws_ts)))::interval)  < '03:00:00'
            then 1
            else 0 
                end

    )as "Lead called between 1 and 3 hours",


    sum(
        case
            when ((  ((dcl.call_start) - (lsl.ws_ts)))::interval) > '03:00:00' and ((  ((dcl.call_start) - (lsl.ws_ts)))::interval)  < '05:00:00'
            then 1
            else 0 
                end

    )as "Lead called between 3 and 5 hours",

    sum(
        case
            when ((  ((dcl.call_start) - (lsl.ws_ts)))::interval) > '05:00:00' 
            then 1
            else 0  
                end

    )as "Lead called after 5 hours"


    from public.leads lsl 
    inner join dialer.dialer_call_logs dcl on (lsl.ws_contact_id::int = dcl.contact_id )
    where lsl.ws_source = 'CAMPAIGN'  and lsl.ws_ts::date between '2020-03-09' and '2020-03-13' and lsl.ws_type <> 'call' and dcl."source" = 'CAMPAIGN'
    group by lsl.ws_creation

The result that I get:

|Date|Lead called before 1 hour|Lead called between 1 and 3 hours|Lead called between 3 and 5 hours|Lead called after 5 hours|
|---|---------------------------------------|--------------------------------------|--------------------------------------|-------------------------------------|
|2020-03-09|118|32|23|426|
|2020-03-10|119|21|26|455|
|2020-03-11|154|39|28|667|
|2020-03-12|146|28|23|442|
|2020-03-13|72|20|21|223|

Upvotes: 1

Views: 282

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

One method is a lateral join:

from public.leads lsl cross join lateral
     (select dcl.*
      from dialer.dialer_call_logs dcl 
      where ws_contact_id::int = contact_id
      order by dcl.call_start asc
      limit 1
     ) dcl
where ws_source = 'CAMPAIGN' and
      ws_ts::date between '2020-03-09' and '2020-03-13' and
      ws_type <> 'call' and dcl."source" = 'CAMPAIGN'

You haven't specified where the columns come from. Some of the where conditions might need to be in the subquery.

Upvotes: 1

Related Questions