repos / pico

commit

9013a2b

parent

6866c6a

author

Eric Bower

date

2024-04-02 18:19:48 +0000 UTC

fix(analytics): better analytic queries

 1@@ -15,15 +15,16 @@ func main() {
 2 	dbpool := postgres.NewDB(DbURL, logger)
 3 
 4 	args := os.Args
 5-	userID := args[1]
 6+	fkID := args[1]
 7 
 8 	stats, err := dbpool.VisitSummary(
 9 		&db.SummaryOpts{
10-			FkID:     userID,
11+			FkID: fkID,
12+			// By:   "post_id",
13 			By:       "user_id",
14 			Interval: "day",
15 			Origin:   shared.StartOfMonth(),
16-			Where:    "AND post_id IS NOT NULL OR (post_id IS NULL AND project_id IS NULL)",
17+			// Where:    "AND (post_id IS NOT NULL OR (post_id IS NULL AND project_id IS NULL))",
18 		},
19 	)
20 	if err != nil {
21@@ -43,7 +44,7 @@ func main() {
22 	for _, url := range stats.TopUrls {
23 		logger.Info(
24 			"url",
25-			"path", url.Url,
26+			"url", url.Url,
27 			"count", url.Count,
28 			"postID", url.PostID,
29 			"projectID", url.ProjectID,
30@@ -53,7 +54,7 @@ func main() {
31 	for _, url := range stats.TopReferers {
32 		logger.Info(
33 			"referer",
34-			"path", url.Url,
35+			"url", url.Url,
36 			"count", url.Count,
37 			"postID", url.PostID,
38 			"projectID", url.ProjectID,

 1@@ -1029,7 +1029,7 @@ func (me *PsqlDB) visitUniqueBlog(opts *db.SummaryOpts) ([]*db.VisitInterval, er
 2 		date_trunc('%s', created_at) as interval_start,
 3         count(DISTINCT ip_address) as unique_visitors
 4 	FROM analytics_visits
 5-	WHERE %s=$1 AND created_at >= $2 %s
 6+	WHERE %s=$1 AND created_at >= $2 AND status <> 404 %s
 7 	GROUP BY interval_start`, opts.Interval, opts.By, opts.Where)
 8 
 9 	intervals := []*db.VisitInterval{}
10@@ -1063,7 +1063,7 @@ func (me *PsqlDB) visitUnique(opts *db.SummaryOpts) ([]*db.VisitInterval, error)
11 		date_trunc('%s', created_at) as interval_start,
12         count(DISTINCT ip_address) as unique_visitors
13 	FROM analytics_visits
14-	WHERE %s=$1 AND created_at >= $2 %s
15+	WHERE %s=$1 AND created_at >= $2 AND status <> 404 %s
16 	GROUP BY post_id, project_id, interval_start`, opts.Interval, opts.By, opts.Where)
17 
18 	intervals := []*db.VisitInterval{}
19@@ -1099,10 +1099,11 @@ func (me *PsqlDB) visitUnique(opts *db.SummaryOpts) ([]*db.VisitInterval, error)
20 func (me *PsqlDB) visitReferer(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
21 	topUrls := fmt.Sprintf(`SELECT
22 		referer,
23-		count(*) as referer_count
24+		count(DISTINCT ip_address) as referer_count
25 	FROM analytics_visits
26-	WHERE %s=$1 AND created_at >= $2 AND referer <> '' %s
27+	WHERE %s=$1 AND created_at >= $2 AND referer <> '' AND status <> 404 %s
28 	GROUP BY referer
29+	ORDER BY referer_count DESC
30 	LIMIT 10`, opts.By, opts.Where)
31 
32 	intervals := []*db.VisitUrl{}
33@@ -1132,10 +1133,13 @@ func (me *PsqlDB) visitReferer(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
34 func (me *PsqlDB) visitUrl(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
35 	topUrls := fmt.Sprintf(`SELECT
36 		path,
37-		count(*) as path_count
38+		count(DISTINCT ip_address) as path_count,
39+		post_id,
40+		project_id
41 	FROM analytics_visits
42-	WHERE %s=$1 AND created_at >= $2 AND path <> '' %s
43-	GROUP BY path
44+	WHERE %s=$1 AND created_at >= $2 AND path <> '' AND status <> 404 %s
45+	GROUP BY path, post_id, project_id
46+	ORDER BY path_count DESC
47 	LIMIT 10`, opts.By, opts.Where)
48 
49 	intervals := []*db.VisitUrl{}
50@@ -1146,13 +1150,19 @@ func (me *PsqlDB) visitUrl(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
51 
52 	for rs.Next() {
53 		interval := &db.VisitUrl{}
54+		var postID sql.NullString
55+		var projectID sql.NullString
56 		err := rs.Scan(
57 			&interval.Url,
58 			&interval.Count,
59+			&postID,
60+			&projectID,
61 		)
62 		if err != nil {
63 			return nil, err
64 		}
65+		interval.PostID = postID.String
66+		interval.ProjectID = projectID.String
67 
68 		intervals = append(intervals, interval)
69 	}