GithubArchive queries for ZFS compression performance tests.

githubarchive_queries.sql

-- Q1. Total row count
SELECT 
    count(*)
FROM
    githubarchive;


-- Q2. Number of rows with language Ruby
SELECT 
    count(*)
FROM
    githubarchive
WHERE
    repository_language = 'Ruby';


-- Q3. Top 10 repos by number of pushes
SELECT 
    repository_name, count(repository_name) as pushes
FROM 
    githubarchive
GROUP BY 
    repository_name
ORDER BY 
    pushes DESC
LIMIT 10;

-- Q4. Distribution of different events on GitHub 
SELECT 
    type, count(type) as cnt
FROM 
    githubarchive
GROUP BY
    type
ORDER BY 
    cnt DESC
LIMIT 10;


-- Q5. Distribution of different repository languages on GitHub 
SELECT 
    repository_language, count(repository_language) as cnt
FROM 
    githubarchive
GROUP BY
    repository_language
ORDER BY
    cnt DESC
LIMIT 10;

-- Q6. Show pagespeed events
SELECT
    count(*)
FROM
    githubarchive
WHERE
    payload_commit_msg LIKE '%pagespeed%';

-- Q7. Aveage repository size
SELECT
    avg(repository_size)
FROM
    githubarchive;