↑错题丨不知道哪个逻辑错了
10月的新户客单价和获客成本
https://www.nowcoder.com/practice/d15ee0798e884f829ae8bd27e10f0d64?tpId=268&tqId=2286131&ru=/exam/oj&qru=/ta/sql-factory-interview/question-ranking&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D268
不知道哪里错了，但是每个表输出一个条件再联结起来比较不会难么容易出错
SELECT
ROUND(AVG(total_amount),1)avg_amount,
ROUND(AVG(sumprice-total_amount),1)avg_cost
FROM
(select order_id,uid,event_time,total_amount,
row_number() over (partition by uid order by event_time)ranking
from tb_order_overall
where status=1
)a
INNER JOIN
(SELECT
order_id,
SUM(price*cnt)sumprice
FROM
tb_order_detail
GROUP BY order_id)b on a.order_id=b.order_id
WHERE ranking=1
AND DATE_FORMAT(event_time,'%Y%m')='202110'