SQL Ranking Functions for Sorting and Aggregating Data for Conversation Data

Question

I'm looking to rank/aggregate conversation data in SQL (specifically BigQuery). The data is conversation data where each row represents one sentence. In the image below I have added the example data for speaker, sentence, and sequence_start. The desired_rank is the target outcome (or something similar).

I believe there should be a window function like a rank/lag/first that should get to the desired rank programmatically.

The closest I got was initially was the following:

WITH DATA AS (
SELECT 'Speaker A' as speaker, 'Sentence 1' as sentence, 1 as sentence_start, 1 as desired_rank
UNION ALL SELECT 'Speaker A' as speaker, 'Sentence 2' as sentence, 9 as sentence_start, 1 as desired_rank
UNION ALL SELECT 'Speaker B' as speaker, 'Sentence 3' as sentence, 27 as sentence_start, 2 as desired_rank
UNION ALL SELECT 'Speaker C' as speaker, 'Sentence 4' as sentence, 46 as sentence_start, 3 as desired_rank
UNION ALL SELECT 'Speaker A' as speaker, 'Sentence 5' as sentence, 78 as sentence_start, 4 as desired_rank
)
SELECT speaker, sentence, sentence_start, desired_rank,

FIRST_VALUE(sentence_start)
  OVER (
    PARTITION BY speaker
    ORDER BY sentence_start
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

FROM DATA
ORDER BY sentence_start

The issue with the result is that Speaker A is always ranked as 1, where it should be 4 (or something similar).

Your help is appreciated. Thanks!

Answer 1

Figured it out. Needed to join down to the next row to determine the change. Added a complication where speaker A speaks for sentence 5 and 6.

WITH data AS (
SELECT          'Speaker A' as speaker, 'Sentence 1' as sentence, 1 as sentence_start, 1 as desired_rank
UNION ALL SELECT 'Speaker A' as speaker, 'Sentence 2' as sentence, 9 as sentence_start, 1 as desired_rank
UNION ALL SELECT 'Speaker B' as speaker, 'Sentence 3' as sentence, 27 as sentence_start, 2 as desired_rank
UNION ALL SELECT 'Speaker C' as speaker, 'Sentence 4' as sentence, 46 as sentence_start, 3 as desired_rank
UNION ALL SELECT 'Speaker A' as speaker, 'Sentence 5' as sentence, 78 as sentence_start, 4 as desired_rank
UNION ALL SELECT 'Speaker A' as speaker, 'Sentence 6' as sentence, 90 as sentence_start, 4 as desired_rank
),
data_ranked AS (
SELECT speaker, sentence, sentence_start, desired_rank,
COALESCE(LEAD(sentence_start) OVER (ORDER BY sentence_start asc),9999999999999) AS next_sentence_start
FROM DATA
ORDER BY sentence_start
),
sentence_information AS (
SELECT sentence_information.speaker, sentence_information.sentence, sentence_information.sentence_start, sentence_information.next_sentence_start
  , CASE WHEN sentence_information.speaker <> next_sentence_information.speaker THEN TRUE ELSE FALSE END as next_sentence_speaker_change_indicator
FROM DATA_RANKED as sentence_information
  LEFT OUTER JOIN DATA AS next_sentence_information ON sentence_information.next_sentence_start = next_sentence_information.sentence_start
),
compiled_sentence_information AS (SELECT sentence_information.speaker, sentence_information.sentence, sentence_information.sentence_start, sentence_information.next_sentence_start
, COALESCE(next_sentence_information.next_sentence_speaker_change_indicator, FALSE) as speaker_change_indicator
, CASE WHEN COALESCE(next_sentence_information.next_sentence_speaker_change_indicator, FALSE) THEN 1 ELSE 0 END as speaker_change_number
, SUM(CASE WHEN COALESCE(next_sentence_information.next_sentence_speaker_change_indicator, FALSE) THEN 1 ELSE 0 END) OVER (ORDER BY sentence_information.sentence_start ASC) AS speaker_sentence_rank
, CASE WHEN sentence_information.next_sentence_start = 9999999999999 THEN TRUE ELSE sentence_information.next_sentence_speaker_change_indicator END as final_sentence_in_paragraph
FROM sentence_information 
  LEFT OUTER JOIN sentence_information as next_sentence_information on sentence_information.sentence_start = next_sentence_information.next_sentence_start
),
paragraphs as (
SELECT *, STRING_AGG(sentence, " ") OVER (PARTITION BY speaker_sentence_rank ORDER BY sentence_start) as paragraph
FROM compiled_sentence_information
)
SELECT speaker, paragraph
FROM paragraphs
WHERE final_sentence_in_paragraph = TRUE
ORDER BY sentence_start