Share the interesting results after you chat with OSS Insight.

[Candicepan]

Welcome to share your talk with OSS Insight and the interesting answers.
e.g: This is what I asked OSS Insight about. And the result took a very short time.

[oliviertassinari]

I have tried to run a custom query

with issue_with_first_responed_at as (
    select
        number, min(date_format(created_at, '%Y-%m-01')) as event_month, min(created_at) as first_responed_at
    from
        github_events ge
    where
        repo_id = 23083156
        and ((type = 'IssueCommentEvent' and action = 'created') or (type = 'IssuesEvent' and action = 'closed'))
        -- Exclude Bots
        -- and actor_login not like '%bot%'
        -- and actor_login not in (select login from blacklist_users bu)
    group by 1
), issue_with_opened_at as (
    select
        number, created_at as opened_at
    from
        github_events ge
    where
        repo_id = 23083156
        and type = 'IssuesEvent'
        and action = 'opened'
        -- Exclude Bots
        -- and actor_login not like '%bot%'
        -- and actor_login not in (select login from blacklist_users bu)
), tdiff as (
    select
        event_month,
        (UNIX_TIMESTAMP(iwfr.first_responed_at) - UNIX_TIMESTAMP(iwo.opened_at)) as diff
    from
        issue_with_opened_at iwo
        join issue_with_first_responed_at iwfr on iwo.number = iwfr.number and iwfr.first_responed_at > iwo.opened_at
), tdiff_with_rank as (
    select
        tdiff.event_month,
        diff  / 60 / 60 as diff,
        ROW_NUMBER() over (partition by tdiff.event_month order by diff) as r,
        count(*) over (partition by tdiff.event_month) as cnt,
        first_value(diff / 60 / 60) over (partition by tdiff.event_month order by diff) as p0,
        first_value(diff / 60 / 60)  over (partition by tdiff.event_month order by diff desc) as p100
    from tdiff
), tdiff_p25 as (
    select
        event_month, diff as p25
    from
        tdiff_with_rank tr
    where
        r = round(cnt * 0.25)
), tdiff_p50 as (
    select
        event_month, diff as p50
    from
        tdiff_with_rank tr
    where
        r = round(cnt * 0.5)
), tdiff_p75 as (
    select
        event_month, diff as p75
    from
        tdiff_with_rank tr
    where
        r = round(cnt * 0.75)
)
select
    distinct tr.event_month,
    p0,
    p25,
    p50,
    p75,
    p100
from tdiff_with_rank tr
left join tdiff_p25 p25 on tr.event_month = p25.event_month
left join tdiff_p50 p50 on tr.event_month = p50.event_month
left join tdiff_p75 p75 on tr.event_month = p75.event_month
order by 1
;

but it fails to detect that repo_id = 23083156 is used.

[Mini256]

@oliviertassinari At present, SQL Playground has some restrictions, which are expected to be lifted this week. I am working on this part.

but it fails to detect that repo_id = 23083156 is used.

😆 But thanks for your report, this rule may ignore the case that the query statement contains CTE statements.

[Candicepan]

This feature is still in beta. We're working hard to make it meet your expectations.
What questions do you want us to answer? It would be great to hear your ideas and feedback.

If you'd like to contribute to us, check out this guide.

[Mini256]

@oliviertassinari Thank you for reporting this bug. I have make a pull request to fix it in PR #1065