Class: UserVisit

Inherits:
ActiveRecord::Base
  • Object
show all
Defined in:
app/models/user_visit.rb

Class Method Summary collapse

Class Method Details

.by_day(start_date, end_date, group_id = nil) ⇒ Object

A count of visits in a date range by day



38
39
40
 
# File 'app/models/user_visit.rb', line 38

def self.by_day(start_date, end_date, group_id = nil)
  counts_by_day_query(start_date, end_date, group_id).count
end

.count_by_active_users(start_date, end_date) ⇒ Object 



15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
# File 'app/models/user_visit.rb', line 15

def self.count_by_active_users(start_date, end_date)
  sql = <<~SQL
    WITH dau AS (
      SELECT date_trunc('day', user_visits.visited_at)::DATE AS date,
             count(distinct user_visits.user_id) AS dau
      FROM user_visits
      WHERE user_visits.visited_at::DATE >= :start_date::DATE AND user_visits.visited_at <= :end_date::DATE
      GROUP BY date_trunc('day', user_visits.visited_at)::DATE
      ORDER BY date_trunc('day', user_visits.visited_at)::DATE
    )

    SELECT date, dau,
      (SELECT count(distinct user_visits.user_id)
        FROM user_visits
        WHERE user_visits.visited_at::DATE BETWEEN dau.date - 29 AND dau.date
      ) AS mau
    FROM dau
  SQL

  DB.query_hash(sql, start_date: start_date, end_date: end_date)
end

.counts_by_day_query(start_date, end_date, group_id = nil) ⇒ Object 



4
5
6
7
8
9
10
11
12
13
 
# File 'app/models/user_visit.rb', line 4

def self.counts_by_day_query(start_date, end_date, group_id = nil)
  result = where("visited_at >= ? and visited_at <= ?", start_date.to_date, end_date.to_date)

  if group_id
    result = result.joins("INNER JOIN users ON users.id = user_visits.user_id")
    result = result.joins("INNER JOIN group_users ON group_users.user_id = users.id")
    result = result.where("group_users.group_id = ?", group_id)
  end
  result.group(:visited_at).order(:visited_at)
end

.ensure_consistency! ⇒ Object 



46
47
48
49
50
51
52
53
54
55
56
57
 
# File 'app/models/user_visit.rb', line 46

def self.ensure_consistency!
  DB.exec <<~SQL
    UPDATE user_stats u set days_visited =
    (
      SELECT COUNT(*) FROM user_visits v WHERE v.user_id = u.user_id
    )
    WHERE days_visited <>
    (
      SELECT COUNT(*) FROM user_visits v WHERE v.user_id = u.user_id
    )
  SQL
end

.mobile_by_day(start_date, end_date, group_id = nil) ⇒ Object 



42
43
44
 
# File 'app/models/user_visit.rb', line 42

def self.mobile_by_day(start_date, end_date, group_id = nil)
  counts_by_day_query(start_date, end_date, group_id).where(mobile: true).count
end