Class: HhClickHouseTopPackage

Inherits:
HhClickHouseBookingBase show all
Defined in:
app/models/hh_click_house_top_package.rb

Overview

HhClickHouseTopPackage provides methods to calculate top-selling packages for restaurants using ClickHouse for high-performance analytics.

This replaces the PostgreSQL materialized view approach with direct ClickHouse queries for better performance and real-time data access.

The calculation ranks packages by quantity sold in the last 30 days, returning the top package (rank 1) for each restaurant.

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from HhClickHouseBookingBase

find

Class Method Details

.calculate_for_all_restaurants(days: 30) ⇒ Hash

Calculate top packages for all active restaurants Returns a hash keyed by restaurant_id with top package information

Parameters:

  • days (Integer) (defaults to: 30) —

    Number of days to look back (default: 30)

Returns:

  • (Hash) —

    Hash keyed by restaurant_id with top package data

Raises:

  • (ArgumentError) 


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
 
# File 'app/models/hh_click_house_top_package.rb', line 88

def self.calculate_for_all_restaurants(days: 30)
  raise ArgumentError, 'days must be between 1 and 365' unless days.between?(1, 365)

  last_n_days = days.days.ago.to_date

  sql = <<-SQL.squish
    SELECT
      ps.restaurant_id,
      ps.package_id,
      ps.package_type,
      ps.quantity_sold,
      ps.rank
    FROM (
      SELECT
        package_id,
        package_type,
        restaurant_id,
        quantity_sold,
        ROW_NUMBER() OVER (PARTITION BY restaurant_id ORDER BY quantity_sold DESC) AS rank
      FROM (
        SELECT
          r.restaurant_id AS restaurant_id,
          rrp.package_id AS package_id,
          rrp.package_type AS package_type,
          SUM(rp.quantity) AS quantity_sold
        FROM reservations r
        INNER JOIN reservation_packages rp ON r.id = rp.reservation_id
        INNER JOIN (
          SELECT
            id,
            package_id,
            package_type,
            restaurant_id,
            active,
            end_date
          FROM hh_package_restaurant_packages
          WHERE (id, end_date) IN (
            SELECT id, MAX(end_date)
            FROM hh_package_restaurant_packages
            GROUP BY id
          )
        ) rrp ON rp.restaurant_package_id = rrp.id
        WHERE r.created_at >= #{connection.quote(last_n_days)}
          AND rrp.active = 1
          AND rrp.end_date > today()
        GROUP BY
          r.restaurant_id,
          rrp.package_id,
          rrp.package_type
      ) AS sales
    ) AS ps
    WHERE ps.rank = 1
  SQL

  results = connection.select_all(sql).to_a
  results.group_by { |row| row['restaurant_id'] }.
    transform_values { |rows| rows.map(&:symbolize_keys) }
end

.calculate_for_restaurant(restaurant_id, days: 30) ⇒ Array<Hash>

Calculate top packages for a specific restaurant Returns an array of hashes with package information and quantity sold

Parameters:

  • restaurant_id (Integer) —

    The restaurant ID to calculate top packages for

  • days (Integer) (defaults to: 30) —

    Number of days to look back (default: 30)

Returns:

  • (Array<Hash>) —

    Array of top packages with keys:

    • restaurant_id: Integer

    • package_id: Integer

    • package_type: String

    • quantity_sold: Integer

    • rank: Integer (always 1 for top package)

Raises:

  • (ArgumentError) 


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
 
# File 'app/models/hh_click_house_top_package.rb', line 25

def self.calculate_for_restaurant(restaurant_id, days: 30)
  raise ArgumentError, 'days must be between 1 and 365' unless days.between?(1, 365)

  last_n_days = days.days.ago.to_date

  sql = <<-SQL.squish
    SELECT
      ps.restaurant_id,
      ps.package_id,
      ps.package_type,
      ps.quantity_sold,
      ps.rank
    FROM (
      SELECT
        package_id,
        package_type,
        restaurant_id,
        quantity_sold,
        ROW_NUMBER() OVER (PARTITION BY restaurant_id ORDER BY quantity_sold DESC) AS rank
      FROM (
        SELECT
          r.restaurant_id AS restaurant_id,
          rrp.package_id AS package_id,
          rrp.package_type AS package_type,
          SUM(rp.quantity) AS quantity_sold
        FROM reservations r
        INNER JOIN reservation_packages rp ON r.id = rp.reservation_id
        INNER JOIN (
          SELECT
            id,
            package_id,
            package_type,
            restaurant_id,
            active,
            end_date
          FROM hh_package_restaurant_packages
          WHERE (id, end_date) IN (
            SELECT id, MAX(end_date)
            FROM hh_package_restaurant_packages
            GROUP BY id
          )
        ) rrp ON rp.restaurant_package_id = rrp.id
        WHERE r.restaurant_id = #{connection.quote(restaurant_id)}
          AND r.created_at >= #{connection.quote(last_n_days)}
          AND rrp.active = 1
          AND rrp.end_date > today()
        GROUP BY
          r.restaurant_id,
          rrp.package_id,
          rrp.package_type
      ) AS sales
    ) AS ps
    WHERE ps.rank = 1
  SQL

  connection.select_all(sql).to_a.map(&:symbolize_keys)
end

Instance Method Details

#readonly? ⇒ Boolean

Returns:

  • (Boolean) 


147
148
149
 
# File 'app/models/hh_click_house_top_package.rb', line 147

def readonly?
  true
end