[solvesql] 보통 풀이

일별 블로그 방문자 수 집계

select event_date_kst as dt,count(distinct(user_pseudo_id)) as users
from ga
where event_date_kst between '2021-08-02' and '2021-08-09'
group by event_date_kst

가구 판매의 비중이 높았던 날 찾기

• 다시 풀기~

SELECT 
order_date AS "order_date", Furniture, ROUND((furniture*1.0/ALL_ORDERS*100), 2) AS furniture_pct
FROM
(
SELECT ORDER_DATE
, COUNT(DISTINCT ORDER_ID) ALL_ORDERS
, (SELECT COUNT(DISTINCT ORDER_ID) FROM RECORDS A WHERE CATEGORY = 'Furniture' AND A.ORDER_DATE = Z.ORDER_DATE) AS furniture
FROM RECORDS Z
GROUP BY ORDER_DATE
HAVING ALL_ORDERS >= 10
)
WHERE ROUND((furniture*1.0/ALL_ORDERS*100), 2) >= 40
ORDER BY ROUND((furniture*1.0/ALL_ORDERS*100), 2) DESC , ORDER_DATE;

지역별 주문의 특징

select region as "Region", 
      (select count(distinct order_id) 
      from records  A where Z.region = A.region and
      category ='Furniture') as Furniture,
      
      (select count(distinct order_id) 
      from records  A where Z.region = A.region and
      category ='Office Supplies') as "Office Supplies",
      
      (select count(distinct order_id) 
      from records  A where Z.region = A.region and
      category ='Technology') as Technology
      
from records Z
group by region
order by region

작품이 없는 작가 찾기

• where 서브쿼리 not in

select artist_id, name
from artists
where artist_id not in (select artist_id from artworks_artists) and death_year not null;

복수 국적 메달 수상한 선수 찾기

• 다시

select name
from athletes a,
  (select athlete_id
  from 
    (select athlete_id, team_id
    from records
    where medal not null 
    and game_id in (select id from games where year >= 2000) 
    group by athlete_id,team_id)
  group by athlete_id having count(team_id)>1) b
where a.id = b.athlete_id
order by name

할부는 몇 개월로 해드릴까요

select payment_installments, 
        count(distinct(order_id)) as order_count,
        min(payment_value) as min_value,
        max(payment_value) as max_value,
        avg(payment_value) as avg_value
from olist_order_payments_dataset
where payment_type = 'credit_card'
group by payment_installments