Gregory Arenius Gregory Arenius -4 years ago 94
SQL Question

How do I count how many rows there were before a certain value?

If I have the following table:

ref_date DATE,
success BOOLEAN

(8, '2016-01-01', FALSE),
(8, '2016-01-04', TRUE),
(8, '2016-01-07', FALSE),
(8, '2016-01-09', FALSE),
(8, '2016-01-15', TRUE),
(9, '2016-01-05', TRUE)

I would like to build a query that returns the following:

8, '2016-01-01', FALSE,
8, '2016-01-04', TRUE, 2
8, '2016-01-07', FALSE,
8, '2016-01-09', FALSE,
8, '2016-01-15', TRUE, 3
9, '2016-01-05', TRUE, 1

What I'm trying to do is, for each c_id, order by date and then for each TRUE value in the success column count how many rows since the first row for that c_id or last TRUE value. So, in the example output, the second row has a two, since when the ref_dates are ordered the first TRUE value was the second row since the start of the group for that c_id. The second to the last row has a 3, because it is TRUE and the third row since the last TRUE value.

I'm trying to count how many referrals it took to get a successful referral.

I originally thought I could build a query using the lag() window function but there is no way to set the preceding bound to the last row with a TRUE value for the success attribute.

How do I count how many rows there were before a certain value?

I'm running PostgreSQL 9.5.

Answer Source

I have a feeling this can be simplified further, but this query will work using:

  • lag to flag the rows that are the start of a different logical grouping
  • cumulative sum to tag all the rows of the same logical grouping with a unique grouping id
  • count that partitions by the logical grouping id


with grp_start_cte as (
  select *,
         coalesce(lag(success) over (partition by c_id order by ref_date), true) as is_grp_start
    from test
), grp_cte as (
  select *,
         sum(case when is_grp_start then 1 else 0 end) over (partition by c_id order by ref_date) as grp_id
    from grp_start_cte
select c_id, ref_date, success,
       case when success then count(*) over (partition by c_id, grp_id) end as cnt
  from grp_cte
 order by c_id, ref_date
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download