solvesqlの質問に答える-1

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

SELECT a.name
FROM games g, records r, athletes a, teams t
WHERE g.id = r.game_id and a.id = r.athlete_id
and r.team_id = t.id and g.year >= 2000 and r.medal is not null
GROUP BY a.id
HAVING count(distinct t.team) > 1
ORDER BY a.name

SELECT date(ood.order_purchase_timestamp) as dt, round(sum(payment_value),3) as revenue_daily
FROM olist_orders_dataset ood JOIN olist_order_payments_dataset  oopd USING(order_id)
WHERE date(ood.order_purchase_timestamp) >= date('2018-01-01')
GROUP BY date(ood.order_purchase_timestamp)

SELECT date(event_date_kst) as dt, count(distinct user_pseudo_id) as users
FROM ga
WHERE date(event_date_kst) between date('2021-08-02') and date('2021-08-09') and user_pseudo_id is is not null
GROUP BY date(event_date_kst)
ORDER BY date(event_date_kst)

SELECT date(order_purchase_timestamp) as dt,
       count(distinct customer_id) as pu,
       round(sum(payment_value),2) as revenue_daily,
       round(sum(payment_value)/count(distinct customer_id),2) as arppu
FROM olist_orders_dataset o1 JOIN olist_order_payments_dataset o2 using (order_id)
WHERE date(order_purchase_timestamp) >= '2018-01-01'
GROUP BY date(order_purchase_timestamp)
ORDER BY order_purchase_timestamp