题解 | #某乎问答回答过教育类问题的用户里有多少...#

--麻了，他说回答过教育再回答职场，我以为还要计算日期先后，狂写40行，不管如何记录一下吧
with
    data as (
        select
            substr((a.issue_id), 1, 1) as type,
            answer_date,
            author_id
        from
            issue_tb as a
            join answer_tb as b on a.issue_id = b.issue_id
    ),
    data2 as (
        select distinct
            author_id,
            type,
            min(answer_date) as answer_date
        from
            data
        group by
            author_id,
            type
        having type != 'p'
        order by
        author_id asc,
        case 
        when type='E' then 1
        else 2
        end
    ),
    data3 as  
    (select
    author_id,type,
    lead(type,1) over( partition by author_id) as type1 ,
    answer_date,
    lead(answer_date,1) over(partition by author_id) as date1
    from
        data2
    )
select
count(*)
from
(
select 
1
from 
data3
group by author_id
having sum(
    case
    when type='E' and type1 is not null then 1
    else 0
    end
)!=0
) as a