1module Statistics |
|
2 class << self |
|
|
3 def moderation(by: "month", parliament: nil) |
4 if parliament |
|
5 table_name = "archived_petitions" |
|
6 else |
|
7 table_name = "petitions" |
|
8 end |
|
10 unless by.in?(%w[week month]) |
|
11 raise ArgumentError, "invalid value for by: #{by.inspect} - must be either week or month" |
|
12 end |
|
14 period = "DATE_TRUNC('#{by}', moderation_threshold_reached_at)" |
|
15 expression = "SUM(CASE WHEN moderation_lag > 7 THEN 0 ELSE 1 END) * 100.0 / COUNT(*)" |
|
17 format = \
|
|
|
18 case by |
19 when "week" |
|
20 "'DD Mon YYYY'" |
|
21 when "month" |
|
22 "'Mon YYYY'" |
|
23 end |
|
25 conditions = []
|
|
26 conditions << "moderation_threshold_reached_at IS NOT NULL" |
|
27 conditions << "moderation_lag IS NOT NULL" |
|
29 if parliament |
|
30 conditions << "parliament_id = #{parliament.id}" |
|
31 end |
|
34 SELECT |
|
35 TO_CHAR(#{period}, #{format}) AS period, |
|
36 ROUND(#{expression}, 1) AS percentage_within_target |
|
37 FROM |
|
38 #{table_name} |
|
39 WHERE |
|
40 #{conditions.join(' AND ')} |
|
41 GROUP BY |
|
42 #{period} |
|
43 ORDER BY |
|
44 #{period} |
|
45 SQL |
|
46 end |
|
48 private
|
|
50 def connection |
|
51 ActiveRecord::Base.connection |
|
52 end |
|
54 def select_rows(sql) |
|
55 connection.select_rows(sql)
|
|
56 end |
|
57 end |
|
58end |