DUE: Monday Apr 10 at 5pm. Submit to Desire2Learn Dropbox.
The tracked users database contains two tables (user and tracker). The DDL for these two tables as well as sample data are included in the tracked_users_data.sql file linked above. The goal of this homework is to correctly write and execute the following queries against the sample database using a MySQL db management system.
To get started you will need to create a database in your mysql server. E.g.,
mysql> create database track_users_db;
You will then need to “activate” the db,
mysql> use track_users_db;
You can “execute” the sql file as:
mysql> source thesqlfilehere.sql
Now you’re ready to go!
NOTE: In mysql the sql syntax for joining two or more tables expects the
joinkeyword.
select * from table1 join table2 on …
/* Sample result at the end of the result. You should have the same number of rows.
| Zajac-Beck | L | 2017-03-08 | 1 |
| Zajac-Beck | L | 2017-04-03 | 1 |
| Zajac-Beck | L | 2017-04-04 | 2 |
| Zamora | E | 2017-03-28 | 1 |
| Zimmerman | L | 2016-11-08 | 1 |
| Zimmerman | L | 2016-11-22 | 1 |
| Zimmerman | L | 2016-11-03 | 1 |
| Zumwalt | T | 2015-12-23 | 1 |
| Zumwalt | T | 2015-12-26 | 1 |
| Zumwalt | T | 2015-12-29 | 1 |
+-------------+-----+------------+----+
10593 rows in set (0.09 sec)
*/
/* Sample result. The column headers may be different but you should have the same result.
+---------+-------+
| average | total |
+---------+-------+
| 20.7425 | 15142 |
+---------+-------+
1 row in set (0.01 sec)
*/
/* Correct output should be listed as:
+--------+----------------------+
| month | average_logins_month |
+--------+----------------------+
| 201504 | 5.0274 |
| 201505 | 6.6173 |
| 201506 | 5.9266 |
| 201507 | 4.5269 |
| 201508 | 4.5500 |
| 201509 | 6.0323 |
| 201510 | 6.6574 |
| 201511 | 5.2952 |
| 201512 | 5.5765 |
| 201601 | 5.5106 |
| 201602 | 5.1932 |
| 201603 | 6.0676 |
| 201604 | 8.4714 |
| 201605 | 7.5942 |
| 201606 | 9.1613 |
| 201607 | 6.9348 |
| 201608 | 7.3692 |
| 201609 | 7.8108 |
| 201610 | 8.4725 |
| 201611 | 7.4082 |
| 201612 | 7.4773 |
| 201701 | 8.0588 |
| 201702 | 7.8175 |
| 201703 | 7.3977 |
| 201704 | 2.3333 |
+--------+----------------------+
25 rows in set (0.02 sec)
*/
-- hw7 queries
-- drop view num_logins_user_day;
-- drop view daily_logins_view;
-- drop view daily_logins_average_view;
-- drop view monthly_logins_view_nonview;
-- drop view num_logins_user_month;
-- drop view monthly_logins_view;
/* Query 1: Write a query that shows the number of logins per user per day. The query should show the last name and first initial of the user, followed by the day, followed by the number of logins (on that day). Order results by users last name in ascending order. */
create or replace view num_logins_user_day as
select
users.last_name as lname,
users.first_initial as finitial,
tracker.event_date as day,
count(tracker.event_date) as logins
from users join tracker
on users.pk = tracker.user_pk
group by users.pk, day
order by lname, finitial;
/* Sample result at the end of the result. You should have the same number of rows.
| Zajac-Beck | L | 2017-03-08 | 1 |
| Zajac-Beck | L | 2017-04-03 | 1 |
| Zajac-Beck | L | 2017-04-04 | 2 |
| Zamora | E | 2017-03-28 | 1 |
| Zimmerman | L | 2016-11-08 | 1 |
| Zimmerman | L | 2016-11-22 | 1 |
| Zimmerman | L | 2016-11-03 | 1 |
| Zumwalt | T | 2015-12-23 | 1 |
| Zumwalt | T | 2015-12-26 | 1 |
| Zumwalt | T | 2015-12-29 | 1 |
+-------------------+---------------+------------+--------+
10593 rows in set (0.09 sec)
*/
/* Query 2: Write a query that shows the average number of daily logins. The result should be one row that shows the average and the total number of logins. HINT: you will need to find the number of logins per day first. Then, take the average of those values. */
create or replace view daily_logins_view as
select event_date, count(event_date) as num_logins
from tracker
group by event_date;
create or replace view daily_logins_average_view as
select avg(num_logins) as average, sum(num_logins) as total
from daily_logins_view;
/* Sample result. The column headers may be different but you should have the same result.
+---------+-------+
| average | total |
+---------+-------+
| 20.7425 | 15142 |
+---------+-------+
1 row in set (0.01 sec)
*/
/* Query 3: Write a query that shows the average number of user logins for each month. For example, if 3 users logged in for a total of 21 times in December, 2016 then the average for that month would be 21/3 = 7 Note, you may need a nested query to aggregate users logins for each month then compute the average per month. Order by month ascending. Show the average and the month.*/
create or replace view monthly_logins_view_nonview as
select month as m, avg(logins) as average_logins_month
from (
select
extract(year_month from event_date) as month,
count(users.pk) as logins
from users join tracker
on users.pk = tracker.user_pk
group by users.pk, month
) as q
group by m
order by m;
/* Query 4 */
create or replace view num_logins_user_month as
select
extract(year_month from event_date) as month,
count(users.pk) as logins
from users join tracker
on users.pk = tracker.user_pk
group by users.pk, month;
create or replace view monthly_logins_view as
select month,
avg(logins) as average_logins_month
from num_logins_user_month
group by month
order by month;
/* Correct output should be listed as:
+--------+----------------------+
| month | average_logins_month |
+--------+----------------------+
| 201504 | 5.0274 |
| 201505 | 6.6173 |
| 201506 | 5.9266 |
| 201507 | 4.5269 |
| 201508 | 4.5500 |
| 201509 | 6.0323 |
| 201510 | 6.6574 |
| 201511 | 5.2952 |
| 201512 | 5.5765 |
| 201601 | 5.5106 |
| 201602 | 5.1932 |
| 201603 | 6.0676 |
| 201604 | 8.4714 |
| 201605 | 7.5942 |
| 201606 | 9.1613 |
| 201607 | 6.9348 |
| 201608 | 7.3692 |
| 201609 | 7.8108 |
| 201610 | 8.4725 |
| 201611 | 7.4082 |
| 201612 | 7.4773 |
| 201701 | 8.0588 |
| 201702 | 7.8175 |
| 201703 | 7.3977 |
| 201704 | 2.3333 |
+--------+----------------------+
25 rows in set (0.02 sec)
*/