SELECT
    language,
    COUNT(repository_id) AS repository_count
FROM (
    SELECT DISTINCT
        language(t.tree_entry_name, b.blob_content) AS language,
        r.repository_id
    FROM
        refs r
        JOIN commits c ON r.commit_hash = c.commit_hash
        JOIN commit_trees ct ON c.commit_hash = ct.commit_hash
        JOIN tree_entries t ON ct.tree_hash = t.tree_hash
        JOIN blobs b ON t.blob_hash = b.blob_hash
    WHERE
        r.ref_name REGEXP 'refs/heads/HEAD/.*' OR r.ref_name = 'HEAD'
) AS q1
GROUP BY language
ORDER BY repository_count DESC;
                        
                    

                        
SELECT repository_id, cf.file_path, f.blob_size
FROM ref_commits r
NATURAL JOIN commit_files cf
NATURAL JOIN files f
WHERE r.ref_name = 'HEAD'
AND r.history_index = 0
ORDER BY f.blob_size DESC
                        
                    

                        
SELECT repository_id, num_files FROM (
  SELECT COUNT(f.*) num_files, f.repository_id
  FROM ref_commits r
  NATURAL JOIN commit_files cf
  NATURAL JOIN files f
  WHERE r.ref_name = 'HEAD' GROUP BY f.repository_id
) AS t
ORDER BY num_files DESC
                        
                    

                        
SELECT
    ref_commits.repository_id, files.file_path, files.blob_content
FROM
    files
NATURAL JOIN
    commit_files
NATURAL JOIN
    ref_commits
WHERE
    ref_commits.ref_name = 'HEAD'
    AND ref_commits.history_index = 0
    AND is_binary(blob_content) = false
    AND files.file_path NOT REGEXP '^vendor.*'
    AND (
        blob_content REGEXP '\\b(?i)(to do|to dos|todo|todos|to-do|to-dos|to_do|to_dos)\\b'
    );
                        
                    

                        
SELECT
    year,
    COUNT(q.commit_hash) AS project_count
FROM (
    SELECT DISTINCT
        r.commit_hash,
        YEAR(commit_author_when) AS year
    FROM
        refs r
        NATURAL JOIN commits c
    WHERE
        r.ref_name REGEXP 'refs/heads/HEAD/*' OR r.ref_name = 'HEAD'
    ) AS q
GROUP BY year
ORDER BY year DESC;
                        
                    

                        
SELECT r.repository_id, commit_author_name, commit_author_when, commit_message
FROM   refs r
       NATURAL JOIN commits
WHERE  r.ref_name = 'HEAD'
ORDER BY commit_author_when
                        
                    

                        
SELECT * FROM (
  SELECT
    commit_author_email as author,
    count(*) as num_commits
    FROM commits
    GROUP BY commit_author_email
) AS t
ORDER BY num_commits DESC
                        
                    

                        
SELECT repository_id, count(*) as contributor_count
FROM (
    SELECT DISTINCT repository_id, commit_author_email
    FROM commits
) AS q
GROUP BY repository_id
ORDER BY contributor_count DESC
LIMIT 50;
                        
                    

                        
SELECT * FROM (
  SELECT
    commit_author_email as author,
    count(*) as num_commits
    FROM commits
    GROUP BY commit_author_email
) AS t
WHERE author NOT REGEXP '@sourced.tech'
ORDER BY num_commits DESC
                        
                    

                        
SELECT repository_id, blob_content
FROM files
WHERE file_path = 'LICENSE'
AND blob_content REGEXP 'Apache License'
                        
                    

                        
select
    files.file_path, ref_commits.repository_id, files.blob_content
FROM
    files
NATURAL JOIN
    commit_files
NATURAL JOIN
    ref_commits
WHERE
    ref_commits.ref_name = 'HEAD'
    AND ref_commits.history_index BETWEEN 0 AND 5
    AND is_binary(blob_content) = false
    AND files.file_path NOT REGEXP '^vendor.*'
    AND (
        blob_content REGEXP '(?i)facebook.*[\'\\"][0-9a-f]{32}[\'\\"]'
        OR blob_content REGEXP '(?i)twitter.*[\'\\"][0-9a-zA-Z]{35,44}[\'\\"]'
        OR blob_content REGEXP '(?i)github.*[\'\\"][0-9a-zA-Z]{35,40}[\'\\"]'
        OR blob_content REGEXP 'AKIA[0-9A-Z]{16}'
        OR blob_content REGEXP '(?i)heroku.*[0-9A-F]{8}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{12}'
        OR blob_content REGEXP '.*-----BEGIN PRIVATE KEY-----.*'
        OR blob_content REGEXP '.*-----BEGIN RSA PRIVATE KEY-----.*'
        OR blob_content REGEXP '.*-----BEGIN DSA PRIVATE KEY-----.*'
        OR blob_content REGEXP '.*-----BEGIN OPENSSH PRIVATE KEY-----.*'
    );
                        
                    

                        
SELECT blob_content FROM files WHERE file_path = 'LICENSE'