sqlite

select release.name, min(release_info.date_year) 
from (release inner join release_info on release.id = release_info.release) 
inner join artist_credit on release.artist_credit = artist_credit.id 
inner join medium on medium.release = release.id
inner join artist_credit_name on artist_credit_name.artist_credit = artist_credit.id
inner join artist on artist_credit_name.artist = artist.id
where artist.name = "The Beatles" and medium.format = 31 and release_info.date_year <= artist.end_date_year and release_info.area = 221
group by release.name
order by release_info.date_year, release.name;


SELECT release.name, MIN(release_info.date_year)
FROM release
JOIN release_info ON release.id = release_info.release
JOIN artist_credit ON release.artist_credit = artist_credit.id
JOIN medium ON medium.release = release.id
JOIN artist_credit_name ON artist_credit_name.artist_credit = artist_credit.id
JOIN artist ON artist_credit_name.artist = artist.id
WHERE artist.name = 'The Beatles' AND medium.format = 31 AND release_info.date_year <= artist.end_date_year AND release_info.area = 221
GROUP BY release.name
ORDER BY MIN(release_info.date_year), release.name;


select release.name, artist_credit.name, release_info.date_year
from release inner join release_info on release.id = release_info.release
inner join artist_credit on release.artist_credit = artist_credit.id
inner join medium on release.id = medium.release
inner join medium_format on medium.format = medium_format.id
where medium_format.name = "Cassette"
order by release_info.date_year desc, release_info.date_month desc, release_info.date_day desc, release.name asc, artist_credit.name asc
limit 10;


SELECT release.name, artist_credit.name, release_info.date_year
FROM release
JOIN release_info ON release.id = release_info.release
JOIN artist_credit ON release.artist_credit = artist_credit.id
JOIN medium ON release.id = medium.release
JOIN medium_format ON medium.format = medium_format.id
WHERE medium_format.name = 'Cassette'
ORDER BY release_info.date_year DESC, release_info.date_month DESC, release_info.date_day DESC, release.name ASC, artist_credit.name ASC
LIMIT 10;


WITH NEWTABLE1 AS (
    SELECT work_type.name AS WORK_TYPE, work.name AS WORK_NAME, LENGTH(work.comment) AS COMMENT_LENGTH, work.comment AS COMMENT
    FROM work INNER JOIN work_type ON work.type = work_type.id
    WHERE LENGTH(work.comment) > 0
),
NEWTABLE2 AS (
    SELECT WORK_TYPE, WORK_NAME, COMMENT_LENGTH, COMMENT, ROW_NUMBER() OVER (PARTITION BY WORK_TYPE ORDER BY COMMENT_LENGTH DESC, WORK_NAME ASC) AS rn
    FROM NEWTABLE1
),
NEWTABLE3 AS (
	SELECT WORK_TYPE, WORK_NAME, COMMENT_LENGTH, COMMENT
	FROM NEWTABLE2
	WHERE rn <= 2
	ORDER BY WORK_TYPE ASC, WORK_NAME ASC
),
NEWTABLE4 AS (
	SELECT WORK_TYPE, MAX(COMMENT_LENGTH) AS MAX_LENGTH
	FROM NEWTABLE2
	GROUP BY WORK_TYPE
)
SELECT NEWTABLE3.WORK_TYPE, WORK_NAME, COMMENT_LENGTH, COMMENT
FROM NEWTABLE3 INNER JOIN NEWTABLE4 
WHERE NEWTABLE3.WORK_TYPE = NEWTABLE4.WORK_TYPE AND NEWTABLE3.COMMENT_LENGTH = NEWTABLE4.MAX_LENGTH;



WITH NEWTABLE1 AS (
    SELECT work_type.name AS WORK_TYPE, work.name AS WORK_NAME, LENGTH(work.comment) AS COMMENT_LENGTH, work.comment AS COMMENT
    FROM work 
    INNER JOIN work_type ON work.type = work_type.id
    WHERE LENGTH(work.comment) > 0
),
NEWTABLE2 AS (
    SELECT WORK_TYPE, WORK_NAME, COMMENT_LENGTH, COMMENT, ROW_NUMBER() OVER (PARTITION BY WORK_TYPE ORDER BY COMMENT_LENGTH DESC, WORK_NAME ASC) AS rn
    FROM NEWTABLE1
),
NEWTABLE3 AS (
    SELECT WORK_TYPE, WORK_NAME, COMMENT_LENGTH, COMMENT
    FROM NEWTABLE2
    WHERE rn <= 2
    ORDER BY WORK_TYPE ASC, WORK_NAME ASC
),
NEWTABLE4 AS (
    SELECT WORK_TYPE, MAX(COMMENT_LENGTH) AS MAX_LENGTH
    FROM NEWTABLE2
    GROUP BY WORK_TYPE
)
SELECT NT3.WORK_TYPE, NT3.WORK_NAME, NT3.COMMENT_LENGTH, NT3.COMMENT
FROM NEWTABLE3 AS NT3
INNER JOIN NEWTABLE4 AS NT4 ON NT3.WORK_TYPE = NT4.WORK_TYPE AND NT3.COMMENT_LENGTH = NT4.MAX_LENGTH
ORDER BY NT3.WORK_TYPE, NT3.WORK_NAME;


WITH TEMP AS (
    SELECT DISTINCT release.id AS RELEASE_ID, release.artist_credit AS RELEASE_ARTIST_CREDIT, release_info.date_month AS DATE_MONTH 
    FROM release INNER JOIN release_info ON release.id = release_info.release
),
NEWTABLE1 AS (
    SELECT artist.name AS ARTIST_NAME, TEMP.DATE_MONTH AS RELEASE_MONTH, count(*) AS COUNT_NUMBER
    FROM TEMP
    INNER JOIN artist_credit ON TEMP.RELEASE_ARTIST_CREDIT = artist_credit.id
    INNER JOIN artist_credit_name ON artist_credit.id = artist_credit_name.artist_credit
    INNER JOIN artist ON artist_credit_name.artist = artist.id
    WHERE artist.type = 1 AND TEMP.DATE_MONTH is not null AND artist.name like 'Elvis%'
    GROUP BY ARTIST_NAME, RELEASE_MONTH
)
SELECT ARTIST_NAME, RELEASE_MONTH, MAX(COUNT_NUMBER)
FROM NEWTABLE1
GROUP BY ARTIST_NAME
ORDER BY COUNT_NUMBER DESC, ARTIST_NAME ASC;