240806_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 코드카타

문제 링크
간단한 cross join 문제.

SELECT t1.team_name AS "home_team",
       t2.team_name AS "away_team"
FROM   teams t1,
       teams t2
WHERE  t1.team_name <> t2.team_name;

문제 링크

WITH a
     AS (SELECT p.invoice_id,
                Sum(p.quantity * pr.price) AS "price2"
         FROM   purchases p
                INNER JOIN products pr
                        ON p.product_id = pr.product_id
         GROUP  BY 1),
     b
     AS (SELECT invoice_id,
                price2,
                Rank()
                  OVER(
                    ORDER BY price2 DESC, invoice_id ASC) AS "ranking"
         FROM   a)
SELECT p.product_id,
       p.quantity,
       ( p.quantity * pr.price ) AS "price"
FROM   purchases p
       INNER JOIN products pr
               ON p.product_id = pr.product_id
       INNER JOIN b
               ON p.invoice_id = b.invoice_id
WHERE  ranking = '1';