-
Notifications
You must be signed in to change notification settings - Fork 12
/
anands_questions.txt
63 lines (52 loc) · 1.69 KB
/
anands_questions.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
Mobile_No Plan Month
2223456 199 January
2223456 399 February
2223456 299 March
2223456 499 April
2223456 299 May
2223456 299 June
2223456 299 July
2223456 299 August
2223456 299 September
2223456 299 October
2223456 299 November
2223456 299 December
2223451 599 January
2223451 199 February
2223451 299 March
2223451 399 April
2223451 299 May
2223451 299 June
2223451 299 July
2223451 299 August
2223451 299 September
2223451 299 October
2223451 299 November
2223451 299 December
2224551 599 January
2224551 299 February
2224551 299 March
2224551 399 April
2224551 199 May
2224551 299 June
2224551 299 July
2224551 299 August
2224551 299 September
2224551 299 October
2224551 299 November
2224551 299 December
Ques 2: Which of the plan is favorite among all plans/which is used by most of the members?
select cellno, plan_type, count(1) sub_count from plans
group by cellno, plan_type with rollup;
select cellno, plan_type, count(1) sub_count from plans
group by cellno, plan_type with cube;
Which Plan used by a member in most of the time?
==========================================================
1)
with t as (select cellno, plan_type, count(1) sub_count from plans group by cellno, plan_type)
select * from t join (select cellno, max(sub_count) max from t group by cellno) v on v.cellno = t.cellno and t.sub_count = v.max;
2) //performs 7 to 8 times better than query 1
with v as (select cellno, count(plan_type) over (partition by cellno, plan_type order by cellno) plan_count from plans)
select cellno, max(plan_count) from v group by cellno;
Which of the plan is favorite among all plans/which is used by most of the members?
====================================================================================