回答編集履歴
3
訂正
answer
CHANGED
|
@@ -15,4 +15,5 @@
|
|
|
15
15
|
) step2
|
|
16
16
|
where tip_rank <= 2
|
|
17
17
|
group by business_id, user_id
|
|
18
|
-
```
|
|
18
|
+
```
|
|
19
|
+
※文法エラーになっていたので修正。
|
2
訂正
answer
CHANGED
|
@@ -3,11 +3,12 @@
|
|
|
3
3
|
分析関数を用います。
|
|
4
4
|
```SQL
|
|
5
5
|
select business_id, user_id from (
|
|
6
|
+
select *
|
|
6
|
-
|
|
7
|
+
, rank() over(partition by business_id, user_id order by tip_count desc) tip_rank
|
|
7
8
|
from (
|
|
8
|
-
select *, count(*) over(
|
|
9
|
+
select *, count(*) over(partition by business_id, user_id) tip_count
|
|
9
|
-
, count(*) over(
|
|
10
|
+
, count(*) over(partition by business_id) user_count
|
|
10
|
-
, count(*) over(
|
|
11
|
+
, count(*) over(partition by user_id) review_count
|
|
11
12
|
from tip
|
|
12
13
|
) step1
|
|
13
14
|
where user_count >= 2 and review_count >= 200
|
1
推敲
answer
CHANGED
|
@@ -2,7 +2,7 @@
|
|
|
2
2
|
|
|
3
3
|
分析関数を用います。
|
|
4
4
|
```SQL
|
|
5
|
-
select
|
|
5
|
+
select business_id, user_id from (
|
|
6
6
|
select *, rank(partion by business_id, user_id ordr by tip_count desc) tip_rank
|
|
7
7
|
from (
|
|
8
8
|
select *, count(*) over(partion by business_id, user_id) tip_count
|
|
@@ -13,4 +13,5 @@
|
|
|
13
13
|
where user_count >= 2 and review_count >= 200
|
|
14
14
|
) step2
|
|
15
15
|
where tip_rank <= 2
|
|
16
|
+
group by business_id, user_id
|
|
16
17
|
```
|