OSS Insight

SELECT

 `gr`.`repo_name` AS `repo_name`,

 COUNT(*) AS `stars`,

 `gr`.`description` AS `description`

FROM

 `github_events` AS `ge`

 INNER JOIN `github_repos` AS `gr` ON `ge`.`repo_id` = `gr`.`repo_id`

WHERE

 `ge`.`type` = 'WatchEvent'

 AND `ge`.`action` = 'started'

 AND YEAR(`ge`.`created_at`) >= 2020

 AND (

  LOWER(`gr`.`description`) LIKE LOWER('%data science%')

  OR LOWER(`gr`.`description`) LIKE LOWER('%ML%')

 )

GROUP BY

 `gr`.`repo_name`

ORDER BY

 `stars` DESC

LIMIT

 10

SELECT

 DATE_FORMAT(`ge`.`created_at`, '%Y-%m-01') AS `t_month`,

 COUNT(*) AS `stars`

FROM

 `github_events` AS `ge`

WHERE

 `ge`.`type` = 'WatchEvent'

 AND `ge`.`repo_id` IN (

  SELECT

   `repo_id`

  FROM

   `github_repos`

  WHERE

   LOWER(`description`) LIKE LOWER('%mlops%')

 )

GROUP BY

 `t_month`

ORDER BY

 `t_month` ASC

LIMIT

 200

WITH

 `prs` AS (

  SELECT

   DATE_FORMAT(`ge`.`created_at`, '%Y-%m-01') AS `t_month`,

   COUNT(*) AS `prs`

  FROM

   `github_events` AS `ge`

  WHERE

   `ge`.`type` = 'PullRequestEvent'

   AND `ge`.`action` = 'opened'

   AND `ge`.`repo_id` IN (

    SELECT

     `repo_id`

    FROM

     `github_repos`

    WHERE

     `owner_login` = 'twitter'

    LIMIT

     50

   )

  GROUP BY

   `t_month`

  ORDER BY

   `t_month` ASC

 ),

 `issues` AS (

  SELECT

   DATE_FORMAT(`ge`.`created_at`, '%Y-%m-01') AS `t_month`,

   COUNT(*) AS `issues`

  FROM

   `github_events` AS `ge`

  WHERE

   `ge`.`type` = 'IssuesEvent'

   AND `ge`.`action` = 'opened'

   AND `ge`.`repo_id` IN (

    SELECT

     `repo_id`

    FROM

     `github_repos`

    WHERE

     `owner_login` = 'twitter'

    LIMIT

     50

   )

  GROUP BY

   `t_month`

  ORDER BY

   `t_month` ASC

 )

SELECT

 `prs`.`t_month`,

 `prs`.`prs`,

 `issues`.`issues`

FROM

 `prs`

 LEFT JOIN `issues` ON `prs`.`t_month` = `issues`.`t_month`

ORDER BY

 `prs`.`t_month` ASC

LIMIT

 200