gistfile1.sql

-- The tables I assume you have:
CREATE TABLE articles
(
  "Id" serial NOT NULL,
  "Date" date,
  "Article" text,
  "Author" text,
  "Title" text,
  "Points" integer,
  "NumComments" integer,
  "Url" text,
  "ObjectId" integer,
  "Webpage_title" text,
  "MetaDescription" text,
  "MetaKeywords" text,
  "FinalUrl" text,
  "TopImage" text
);

-- Could also be an attribute of the previous table, but this is more in line with the data supply (and allows for multiple topics per article)
CREATE TABLE articletopics
(
  "articleId" integer,
  "topicId" integer
);

-- Descriptions of the topics by providing keywords plus their score (how good they match the topic)
CREATE TABLE topics
(
  "topicId" integer,
  keyword text,
  score double precision
);

-- Create some views that show some statistics
-- (last one is materialized, since it takes like 30 seconds to generate and shouldn't change anyway)

CREATE OR REPLACE VIEW monthlytotalscores AS 
 SELECT date_part('month'::text, articles."Date") AS month,
    date_part('year'::text, articles."Date") AS year,
    sum(articles."Points") AS points,
    sum(articles."NumComments") AS comments,
    count(DISTINCT articles."Id") AS articles
   FROM articles,
    articletopics
  WHERE articles."Id" = articletopics."articleId"
  GROUP BY date_part('month'::text, articles."Date"), date_part('year'::text, articles."Date");

CREATE OR REPLACE VIEW monthlytopicscores AS 
 SELECT mt.topic,
    mt.month,
    mt.year,
        CASE
            WHEN r.points IS NULL THEN 0::bigint
            ELSE r.points
        END AS points,
        CASE
            WHEN r.comments IS NULL THEN 0::bigint
            ELSE r.comments
        END AS comments,
        CASE
            WHEN r.articles IS NULL THEN 0::bigint
            ELSE r.articles
        END AS articles
   FROM ( SELECT t.column1 AS topic,
            m.column1 AS month,
            m.column2 AS year
           FROM ( VALUES (2,2007), (3,2007), (4,2007), (5,2007), (6,2007), (7,2007), (8,2007), (9,2007), (10,2007), (11,2007), (12,2007), (1,2008), (2,2008), (3,2008), (4,2008), (5,2008), (6,2008), (7,2008), (8,2008), (9,2008), (10,2008), (11,2008), (12,2008), (1,2009), (2,2009), (3,2009), (4,2009), (5,2009), (6,2009), (7,2009), (8,2009), (9,2009), (10,2009), (11,2009), (12,2009), (1,2010), (2,2010), (3,2010), (4,2010), (5,2010), (6,2010), (7,2010), (8,2010), (9,2010), (10,2010), (11,2010), (12,2010), (1,2011), (2,2011), (3,2011), (4,2011), (5,2011), (6,2011), (7,2011), (8,2011), (9,2011), (10,2011), (11,2011), (12,2011), (1,2012), (2,2012), (3,2012), (4,2012), (5,2012), (6,2012), (7,2012), (8,2012), (9,2012), (10,2012), (11,2012), (12,2012), (1,2013), (2,2013), (3,2013), (4,2013), (5,2013), (6,2013), (7,2013), (8,2013), (9,2013), (10,2013), (11,2013), (12,2013), (1,2014), (2,2014), (3,2014), (4,2014), (5,2014), (6,2014), (7,2014), (8,2014), (9,2014), (10,2014), (11,2014), (12,2014), (1,2015), (2,2015), (3,2015), (4,2015), (5,2015), (6,2015)) m,
            ( VALUES (0), (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), (93), (94), (95), (96), (97), (98), (99), (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112), (113), (114), (115), (116), (117), (118), (119), (120), (121), (122), (123), (124), (125), (126), (127), (128), (129), (130), (131), (132), (133), (134), (135), (136), (137), (138), (139), (140), (141), (142), (143), (144), (145), (146), (147), (148), (149), (150), (151), (152), (153), (154), (155), (156), (157), (158), (159), (160), (161), (162), (163), (164), (165), (166), (167), (168), (169), (170), (171), (172), (173), (174), (175), (176), (177), (178), (179), (180), (181), (182), (183), (184), (185), (186), (187), (188), (189), (190), (191), (192), (193), (194), (195), (196), (197), (198), (199), (200), (201), (202), (203), (204), (205), (206), (207), (208), (209), (210), (211), (212), (213), (214), (215), (216), (217), (218), (219), (220), (221), (222), (223), (224), (225), (226), (227), (228), (229), (230), (231), (232), (233), (234), (235), (236), (237), (238), (239), (240), (241), (242), (243), (244), (245), (246), (247), (248), (249), (250), (251), (252), (253), (254), (255), (256), (257), (258), (259), (260), (261), (262), (263), (264), (265), (266), (267), (268), (269), (270), (271), (272), (273), (274), (275), (276), (277), (278), (279), (280), (281), (282), (283), (284), (285), (286), (287), (288), (289), (290), (291), (292), (293), (294), (295), (296), (297), (298), (299), (300), (301), (302), (303), (304), (305), (306), (307), (308), (309), (310), (311), (312), (313), (314), (315), (316), (317), (318), (319), (320), (321), (322), (323), (324), (325), (326), (327), (328), (329), (330), (331), (332), (333), (334), (335), (336), (337), (338), (339), (340), (341), (342), (343), (344), (345), (346), (347), (348), (349), (350), (351), (352), (353), (354), (355), (356), (357), (358), (359), (360), (361), (362), (363), (364), (365), (366), (367), (368), (369), (370), (371), (372), (373), (374), (375), (376), (377), (378), (379), (380), (381), (382), (383), (384), (385), (386), (387), (388), (389), (390), (391), (392), (393), (394), (395), (396), (397), (398), (399), (400), (401), (402), (403), (404), (405), (406), (407), (408), (409), (410), (411), (412), (413), (414), (415), (416), (417), (418), (419), (420), (421), (422), (423), (424), (425), (426), (427), (428), (429), (430), (431), (432), (433), (434), (435), (436), (437), (438), (439), (440), (441), (442), (443), (444), (445), (446), (447), (448), (449), (450), (451), (452), (453), (454), (455), (456), (457), (458), (459), (460), (461), (462), (463), (464), (465), (466), (467), (468), (469), (470), (471), (472), (473), (474), (475), (476), (477), (478), (479), (480), (481), (482), (483), (484), (485), (486), (487), (488), (489), (490), (491), (492), (493), (494), (495), (496), (497), (498), (499), (500), (501), (502), (503), (504), (505), (506), (507), (508), (509), (510), (511), (512), (513), (514), (515), (516), (517), (518), (519), (520), (521), (522), (523), (524), (525), (526), (527), (528), (529), (530), (531), (532), (533), (534), (535), (536), (537), (538), (539), (540), (541), (542), (543), (544), (545), (546), (547), (548), (549), (550), (551), (552), (553), (554), (555), (556), (557), (558), (559), (560), (561), (562), (563), (564), (565), (566), (567), (568), (569), (570), (571), (572), (573), (574), (575), (576), (577), (578), (579), (580), (581), (582), (583), (584), (585), (586), (587), (588), (589), (590), (591), (592), (593), (594), (595), (596), (597), (598), (599), (600), (601), (602), (603), (604), (605), (606), (607), (608), (609), (610), (611), (612), (613), (614), (615), (616), (617), (618), (619), (620), (621), (622), (623), (624), (625), (626), (627), (628), (629), (630), (631), (632), (633), (634), (635), (636), (637), (638), (639), (640), (641), (642), (643), (644), (645), (646), (647), (648), (649), (650), (651), (652), (653), (654), (655), (656), (657), (658), (659), (660), (661), (662), (663), (664), (665), (666), (667), (668), (669), (670), (671), (672), (673), (674), (675), (676), (677), (678), (679), (680), (681), (682), (683), (684), (685), (686), (687), (688), (689), (690), (691), (692), (693), (694), (695), (696), (697), (698), (699), (700), (701), (702), (703), (704), (705), (706), (707), (708), (709), (710), (711), (712), (713), (714), (715), (716), (717), (718), (719), (720), (721), (722), (723), (724), (725), (726), (727), (728), (729), (730), (731), (732), (733), (734), (735), (736), (737), (738), (739), (740), (741), (742), (743), (744), (745), (746), (747), (748), (749), (750), (751), (752), (753), (754), (755), (756), (757), (758), (759), (760), (761), (762), (763), (764), (765), (766), (767), (768), (769), (770), (771), (772), (773), (774), (775), (776), (777), (778), (779), (780), (781), (782), (783), (784), (785), (786), (787), (788), (789), (790), (791), (792), (793), (794), (795), (796), (797), (798), (799), (800), (801), (802), (803), (804), (805), (806), (807), (808), (809), (810), (811), (812), (813), (814), (815), (816), (817), (818), (819), (820), (821), (822), (823), (824), (825), (826), (827), (828), (829), (830), (831), (832), (833), (834), (835), (836), (837), (838), (839), (840), (841), (842), (843), (844), (845), (846), (847), (848), (849), (850), (851), (852), (853), (854), (855), (856), (857), (858), (859), (860), (861), (862), (863), (864), (865), (866), (867), (868), (869), (870), (871), (872), (873), (874), (875), (876), (877), (878), (879), (880), (881), (882), (883), (884), (885), (886), (887), (888), (889), (890), (891), (892), (893), (894), (895), (896), (897), (898), (899), (900), (901), (902), (903), (904), (905), (906), (907), (908), (909), (910), (911), (912), (913), (914), (915), (916), (917), (918), (919), (920), (921), (922), (923), (924), (925), (926), (927), (928), (929), (930), (931), (932), (933), (934), (935), (936), (937), (938), (939), (940), (941), (942), (943), (944), (945), (946), (947), (948), (949), (950), (951), (952), (953), (954), (955), (956), (957), (958), (959), (960), (961), (962), (963), (964), (965), (966), (967), (968), (969), (970), (971), (972), (973), (974), (975), (976), (977), (978), (979), (980), (981), (982), (983), (984), (985), (986), (987), (988), (989), (990), (991), (992), (993), (994), (995), (996), (997), (998), (999)) t) mt
     LEFT JOIN ( SELECT date_part('month'::text, articles."Date") AS month,
            date_part('year'::text, articles."Date") AS year,
            sum(articles."Points") AS points,
            sum(articles."NumComments") AS comments,
            count(DISTINCT articles."Id") AS articles,
            topics."topicId" AS topic
           FROM articles,
            articletopics,
            topics
          WHERE articles."Id" = articletopics."articleId" AND articletopics."topicId" = topics."topicId"
          GROUP BY date_part('month'::text, articles."Date"), date_part('year'::text, articles."Date"), topics."topicId"
          ORDER BY date_part('year'::text, articles."Date") DESC, date_part('month'::text, articles."Date") DESC) r ON mt.topic = r.topic AND r.month = mt.month::double precision AND r.year = mt.year::double precision;

CREATE MATERIALIZED VIEW monthlyrelativetopicscores AS 
 SELECT tot.month,
    tot.year,
    top.topic,
    -- Multiply by 10,000 to make them basispoints (per tenthousand)
    top.points * 10000 / tot.points AS pointspromille,
    top.articles * 10000 / tot.articles AS articlespromille,
    top.comments * 10000 / tot.comments AS commentspromille,
    (top.points * 10000 / tot.points + top.articles * 10000 / tot.articles + top.comments * 10000 / tot.comments) / 3 AS popularity
   FROM monthlytopicscores top,
    monthlytotalscores tot
  WHERE top.year = tot.year AND top.month = tot.month
WITH DATA;