-
Notifications
You must be signed in to change notification settings - Fork 0
/
Data_Migration.sql
92 lines (87 loc) · 2.44 KB
/
Data_Migration.sql
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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
-- Enter Database
psql
\connect postgres;
-- Insert all unique usernames from both initial tables.
INSERT INTO "users"
("username")
WITH union_usernames
AS (SELECT username
FROM "bad_posts"
UNION ALL
SELECT username
FROM "bad_comments"),
distinct_usernames
AS (SELECT username
FROM union_usernames)
SELECT DISTINCT username
FROM distinct_usernames
ORDER BY 1 ASC;
-- Insert distinct topics from "bad_posts"
INSERT INTO "topics"
("topic_name")
SELECT DISTINCT topic
FROM "bad_posts";
-- Insert fields from the "bad_posts", "users" and "topics".
INSERT INTO "posts"
("title",
"url",
"text_content",
"user_id",
"topic_id")
SELECT bp.title,
bp.url,
bp.text_content,
uu.id AS user_id,
tp.id AS topic_id
FROM "bad_posts" bp
join "users" uu
ON bp.username = uu.username
join "topics" tp
ON bp.topic = tp.topic_name
WHERE Length(Trim(title)) <= 100;
-- Insert comments and ids in "comments".
INSERT INTO "comments"
("comment",
"user_id",
"topic_id",
"post_id")
SELECT bc.text_content AS COMMENT,
po.user_id,
po.topic_id,
po.id AS post_id
FROM "bad_posts" bp
join "posts" po
ON bp.title = po.title
join "users" uu
ON po.user_id = uu.id
join "bad_comments" bc
ON uu.username = bc.username;
-- Insert upvotes & downvotes in "post_votes".
INSERT INTO "post_votes"
("post_vote",
"voter_user_id",
"post_id")
WITH "bad_posts_upvotes"
AS (SELECT title,
Regexp_split_to_table(bp.upvotes, ',') AS username_upvotes
FROM "bad_posts" bp),
"bad_posts_downvotes"
AS (SELECT title,
Regexp_split_to_table(bp.downvotes, ',') AS username_downvotes
FROM "bad_posts" bp) SELECT 1 AS post_vote,
uu.id AS voter_user_id,
po.id AS post_id
FROM "bad_posts_upvotes" bpu
join "posts" po
ON bpu.title = po.title
join "users" uu
ON bpu.username_upvotes = uu.username
UNION ALL
SELECT -1 AS post_vote,
uu.id AS voter_user_id,
po.id AS post_id
FROM "bad_posts_downvotes" bpd
join "posts" po
ON bpd.title = po.title
join "users" uu
ON bpd.username_downvotes = uu.username;