----- 1 -----
SELECT id, username FROM users
ORDER BY id ASC
----- 2 -----
SELECT repository_id, contributor_id
FROM repositories_contributors
WHERE repository_id = contributor_id
ORDER BY repository_id ASC
----- 3 -----
SELECT i.id,
CONCAT(u.username, ' : ', i.title) AS issue_assignee
FROM issues AS i
INNER JOIN users AS u ON i.assignee_id = u.id
ORDER BY i.id DESC
----- 4-1 -----
SELECT parent_file.id,
parent_file.name,
CONCAT(parent_file.size, 'KB') AS size
FROM files AS parent_file
WHERE NOT EXISTS (
SELECT * FROM files
WHERE parent_id = parent_file.id
ORDER BY id ASC)
ORDER BY id ASC
----- 4-2 -----
SELECT parent_file.id,
parent_file.name,
CONCAT(parent_file.size, 'KB') AS size
FROM files AS parent_file
LEFT OUTER JOIN files AS child_file
ON parent_file.id = child_file.parent_id
WHERE child_file.id IS NULL
ORDER BY parent_file.id ASC
----- 4-3 -----
SELECT parent_file.id,
parent_file.name,
CONCAT(parent_file.size, 'KB') AS size
FROM files AS parent_file
WHERE parent_file.id NOT IN(
	SELECT DISTINCT child_file.parent_id
    FROM files AS child_file
    WHERE child_file.parent_id IS NOT null
)
ORDER BY parent_file.id ASC
----- 5 -----
SELECT r.name,
COUNT(*) AS issues
FROM issues AS i
INNER JOIN repositories AS r
ON r.id = i.repository_id
GROUP BY repository_id
ORDER BY issues DESC, r.id ASC
----- 6 -----
SELECT repository_id,
r.name,
(SELECT COUNT(*) FROM commits AS c
WHERE c.repository_id=r.id) AS commits,
COUNT(*) AS contributors
FROM repositories_contributors AS rc
INNER JOIN repositories AS r ON
rc.repository_id = r.id
GROUP BY repository_id
ORDER BY contributors DESC
LIMIT 1
----- 7 -----
SELECT c.repository_id, r.name, COUNT(*) AS users
FROM commits AS c
INNER JOIN repositories AS r
ON r.id = c.repository_id
GROUP BY repository_id
ORDER BY users DESC, c.repository_id ASC