оо, не пятница, так вечер воскресенья. имхо cte читабельнее с точки зрения того, что можно читать top-down, по минимуму двигая глазами в разные стороны

всё как всегда зависит от субд)

это да. у меня на работе псевдо sql, так там cte выполняются при каждом обращении к ним, а не однократно %(

но обычно все же это однократное выполнение, насколько я знаю

если в одном запросе несколько cte, то всё равно сложно. По-программерски вроде ок - объявили -> используем. Но вот по трансформации данных как-то не очень. Но с подзапросами дургая беда: нужно держать жёсткую дисциплину в плане query style guide

я видел такое, во что вы, люди, просто не поверите. left join на той же строке, что и from, group by 1, 2 для дедупликации, хаотичные отступы в субзапросах...

Ну это примерно как -> в R))

Вроде простенькая задача:
1 сортируем по дате и делаем row_numbers по юзеру
2 выбираем где первые пустыня вторые болота и наооборот,
и все решение как по мне

я сам не идеален, я операторы пишу маленькими буквами, олдфаги бесятся... но тут даже мне плохо было :(

там чуть сложнее, уровней около 100, и с 30 пустыня и болото могут идти в разном порядке, как пользователь решит, просто на первый - второй не разбить
но да, направление примерно такое, спасибо

У меня IDE с маленькой буквы операторы пишет и переводит по автозаполнение. По началу это сильно бесило, поставил стили и  темы теперь они цветом отличаются и привык

ну вот я как раз полагаюсь на цветовую схему при выделении ключевых слов, а не на регистр.

но есть те, кто говорит, что неканон

к слову, рстудия все никак не запилит поддержку sql синтаксиса в r-скриптах, а жаль :(

приходится как простой текст писать :(

я заранее извиняюсь перед всеми, кто пишет на sql часто и/или нормально, слишком не ругайте, я профан

Филипп, развивая тему про string_agg в BQ: уверен, можно написать это лучше (а озвученное решение с лагом выглядит еще лучше), но, как я часто говорю, раз встрял - надо что-то написать.

with tmp as (
    select 1 as user_id, 'swamp' as level, 'lost' as result union all 
    select 1, 'swamp', 'won' union all 
    select 1, 'swamp', 'won' union all 
    select 2, 'desert', 'won' union all 
    select 2, 'swamp', 'list' union all 
    select 3, 'desert', 'lost' union all 
    select 3, 'swamp', 'won'
)
, process_table as (
    select 
        result, 
        string_agg(level, ' > ') over (partition by user_id rows between current row and 1 following ) as process
    from tmp
)

select result as first_result, process, count(*) as N
from process_table
where array_length(split(process, " > ")) > 1
group by result, process
order by count(*) desc

спасибо, чуть позже сегодня попробую!

ну и наркоманию я все-таки написал.
не скажу, что через лаг (лид) у меня получилось лучше, но понять такое, наверное, проще

with tmp as (
    select 1 as user_id, 'swamp' as level, 'lost' as result union all 
    select 1, 'swamp', 'won' union all 
    select 1, 'swamp', 'won' union all 
    select 2, 'desert', 'won' union all 
    select 2, 'swamp', 'list' union all 
    select 3, 'desert', 'lost' union all 
    select 3, 'swamp', 'won'
)

, process_table as (
    select 
        result, 
        level as level_one, 
        lead(level) over (partition by user_id order by user_id) as level_two
    from tmp
)

select
    *, count(*) as N
from process_table
where level_two is not null
group by result, level_one, level_two
order by result, level_one, level_two;

не очень понятно, а почему наружу не вытащить все эти дуплеты  и посчитать отдельно?
сколько там этих кейсов?