os.chdir('/Users/chweng/Desktop/SQLExerciseOnlineMusicPlayer')

"""
Explanation: MySQL
End of explanation
"""


GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'userpasswd' WITH GRANT OPTION;

"""
Explanation: Create an user
While creating an user, we'll see if to give user some privileges such as being able to select columns out of some tables:
End of explanation
"""


CREATE DATABASE exercises

CREATE TABLE userprofile (
    userid VARCHAR(20) NOT NULL,
    gender char(1),
    age int,
    country VARCHAR(20),
    registered VARCHAR(20),
    PRIMARY KEY (userid)
);

LOAD DATA INFILE '/users/raemoen/BC3/userprofile.tsv' 
INTO TABLE userprofile 
FIELDS TERMINATED BY '\t' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
--IGNORE 1 ROWS;

CREATE TABLE userdemand (
    userid VARCHAR(20) NOT NULL,
    time VARCHAR(50),
    artname VARCHAR(100),
    traname VARCHAR(100),
    FOREIGN KEY(userid) REFERENCES userprofile(userid)
);

LOAD DATA INFILE '/users/raemoen/BC3/userdemand.tsv' 
INTO TABLE userdemand 
FIELDS TERMINATED BY '\t' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
--IGNORE 1 ROWS;

CREATE TABLE calendar (
    yMd VARCHAR(100),
    dofw VARCHAR(100),
    wnofy VARCHAR(100)
);

LOAD DATA INFILE '/users/raemoen/BC3/calendar.csv' 
INTO TABLE calendar 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
#IGNORE 1 ROWS;

"""
Explanation: https://www.techonthenet.com/oracle/grant_revoke.php

with grant option: meaning that this user is able to grant privileges for some other people.
*.* means the user can access to anyDB.anyTable.
@'%' means that the user is came from any IP.

Create DB & tables:
commands:


create table mytable


load data infile filepath into table tablename


https://www.tutorialspoint.com/mysql/mysql-data-types.htm
End of explanation
"""


select userprofile.gender as gender,count(distinct(userprofile.userid)) as numMembers,count(userdemand.time) as totalPlayTimes
from userprofile left outer join userdemand
on userprofile.userid=userdemand.userid
group by gender

df=pd.read_csv('q1.csv')

df

sum(df['totalPlayTimes']) # 總點播次數

"""
Explanation: Now, the tables are created. We are ready to practice. Here are my solutions. 
Q1: 男性與女性的會員數量與總點播次數
End of explanation
"""


select userprofile.country as country,count(distinct(userprofile.userid)) as numMembers,count(userdemand.time) as totalPlayTimes
from userprofile left outer join userdemand
on userprofile.userid=userdemand.userid
group by country
order by numMembers desc

df=pd.read_csv('q2.csv')

df.head(5)

sum(df['totalPlayTimes'])  # 目的：驗證各國家點播次數相加是否為總點播次數。

"""
Explanation: Q2: 會員數量排行顯示前五名的國家，和這些國家的總點播次數：
End of explanation
"""


with temp as (select userprofile.country as country,count(distinct(userprofile.userid)) as numMembers,count(userdemand.time) as totalPlayTimes
from userprofile left outer join userdemand
on userprofile.userid=userdemand.userid
group by country
order by numMembers desc
)
select sum(totalPlayTimes) from temp;

"""
Explanation: 正確。各國家點播次數相加為總點播次數。
亦可用mysql指令驗證，如下：
End of explanation
"""


SELECT substring(time,1,10) as date,count(*) as nPlayTimes
FROM exercises.userdemand
group by date
order by nPlayTimes desc;

"""
Explanation: Q3: 哪一天是會員透過我們的音樂網站聆聽音樂最多的一天？
解一：
End of explanation
"""


with temp as 
(select userid,substring(time,1,10) as time 
from userdemand
)
select time,count(*) as nPlayTimes from temp
group by time
order by nPlayTimes desc;

df=pd.read_csv('q3.csv')

df.head(3)

"""
Explanation: 解二(此法不好，因建暫存table是不必要的)：
End of explanation
"""


with tmp as (SELECT userid,age,(case
when age>=0 and age<20 then '0-19'
when age>=20 and age<40 then '20-39'
when age>=40 and age<65 then '40-64'
when age>=65 then '65-'
end) as ageZone
FROM exercises.userprofile)
select ageZone,count(*) as numMembers from tmp group by ageZone

"""
Explanation: Q4: 會員年齡層分布
使用select (case when condition then xxx else xxx end)
解一 (用with as)：
End of explanation
"""


select  t.ages,count(t.age)
from (
select age,
 CASE 
  WHEN age between 0 and 19 THEN "0-19"
  WHEN age between 20 and 39 THEN "20-39"
  WHEN age between 40 and 64 THEN "40-64"
  else "65--"
END ages
FROM exercises.userprofile) t
group by t.ages;

df=pd.read_csv('q4.csv')

df

"""
Explanation: 解二 (用subquery)：
End of explanation
"""


MariaDB [exercises]> select artname,traname,count(distinct(userid)) as listners
    -> from (select date(yMd) as yMd,dofw from calendar where dofw='Sa' or dofw='Su') as cal
    -> join 
    ->      (select userid,substring(time,1,10) as time,artname,traname from userdemand) as usrdemand
    -> on cal.yMd= usrdemand.time
    -> group by artname,traname
    -> order by listners desc, artname asc
    -> limit 5;
+--------------------+-------------------------+----------+
| artname            | traname                 | listners |
+--------------------+-------------------------+----------+
| Boy Division       | Love Will Tear Us Apart |       55 |
| Radiohead          | Creep                   |       51 |
| Massive Attack     | Teardrop                |       50 |
| The Postal Service | Such Great Heights      |       50 |
| Radiohead          | Paranoid Android        |       49 |
+--------------------+-------------------------+----------+
5 rows in set (20 min 10.04 sec)

"""
Explanation: Q5: 從過去所有資料中，找出會員在假日最常聽的三首歌曲
End of explanation
"""


