Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
121 lines (109 sloc) 3.11 KB
******************************************************************************
COMMENTS
******************************************************************************
Calculate the average comments for the users with >= 2 posts, and each post has comments greater or equal to 40
#DDL SCRIPTS
Comments I
name posts comments
u1 page1 90
u1 page2 50
u1 page3 40
u2 page2 55
u2 page4 45
u4 page4 30
u4 page3 40
u3 page2 100
CREATE TABLE comments (
name varchar(80),
posts varchar(80),
comments int
);
INSERT INTO comments VALUES ('u1', 'page1', '90');
INSERT INTO comments VALUES ('u1', 'page2', '50');
INSERT INTO comments VALUES ('u1', 'page3', '40');
INSERT INTO comments VALUES ('u2', 'page2', '55');
INSERT INTO comments VALUES ('u2', 'page4', '45');
INSERT INTO comments VALUES ('u4', 'page4', '30');
INSERT INTO comments VALUES ('u4', 'page3', '40');
INSERT INTO comments VALUES ('u3', 'page2', '100');
# CALCULATE THE AVERAGE COMMENTS FOR THE USERS WITH >= 2 POSTS, AND EACH POST HAS COMMENTS GREATER OR EAUAL TO 40
# Using join
select a.name, round(avg(a.comments),2) as avg_comment
from comments a
join (select name
from comments
where comments >=40
group by name having count(posts) >=2) b
on a.name=b.name group by a.name;
+------+-------------+
| name | avg_comment |
+------+-------------+
| u1 | 60.00 |
| u2 | 50.00 |
+------+-------------+
#Without using join
select name,
avg(comments) as avg_count,
count(posts) as cnt_posts
from comments
where comments >= 40
group by name having count(posts) >=2;
+------+-----------+-----------+
| name | avg_count | cnt_posts |
+------+-----------+-----------+
| u1 | 60.0000 | 3 |
| u2 | 50.0000 | 2 |
+------+-----------+-----------+
Step by step approach
1) AVERAGE COMMENTS FOR THE USERS
2) WITH >= 2 POSTS,
3) AND EACH POST HAS COMMENTS GREATER OR EAUAL TO 40
1)
select name,round(avg(comments),2) as avg_comments
from comments group by name;
+------+--------------+
| name | avg_comments |
+------+--------------+
| u1 | 60.00 |
| u2 | 50.00 |
| u4 | 35.00 |
| u3 | 100.00 |
+------+--------------+
2)
select * from comments
where comments >=40;
+------+-------+----------+
| name | posts | comments |
+------+-------+----------+
| u1 | page1 | 90 |
| u1 | page2 | 50 |
| u1 | page3 | 40 |
| u2 | page2 | 55 |
| u2 | page4 | 45 |
| u4 | page3 | 40 |
| u3 | page2 | 100 |
+------+-------+----------+
3)
select name
from comments
group by name
having count(posts) >=2;
+------+
| name |
+------+
| u1 |
| u2 |
| u4 |
+------+
4)
select name,round(avg(comments),2) as avg_comments
from comments
where comments >=40
group by name
having count(posts) >=2;
+------+--------------+
| name | avg_comments |
+------+--------------+
| u1 | 60.00 |
| u2 | 50.00 |
+------+--------------+