jam-cloud/ruby/lib/jam_ruby/models/jam_class_report.rb

# CREATE FUNCTION jamclass_report RETURNS TABLE (campaign VARCHAR, spend numeric(8,2), registrations INTEGER, td_customers INTEGER, jamclass_rev NUMERIC(8,2), td4 INTEGER, td2 INTEGER, td1 INTEGER, spend_td NUMERIC(8,2), purchases_0 INTEGER, purchases_1 INTEGER, purchases_2 INTEGER, purchases_3 INTEGER, purchases_rest INTEGER) VOLATILE AS $$

module JamRuby
  class JamClassReport < ActiveRecord::Base

    def self.update_spend

    end
    def self.analyse(campaign_filter = nil)

      User.transaction do
        user_purchase = "CREATE TEMPORARY TABLE user_jamclass_purchases (user_id VARCHAR(64) NOT NULL, purchases INTEGER DEFAULT 0) ON COMMIT DROP"
        user_inserts = "INSERT INTO user_jamclass_purchases (user_id, purchases) (SELECT id, COUNT(user_jamclass_purchases.user_id) FROM users LEFT OUTER JOIN user_jamclass_purchases ON users.id = user_jamclass_purchases.user_id GROUP BY users.id)"
        User.connection.execute(user_purchase)
        User.connection.execute(user_inserts)

        jamclass_revenue = "(SELECT SUM(price) * 0.25 FROM lesson_package_purchases WHERE lesson_package_purchases.lesson_package_type_id = 'single') + (SELECT SUM(6) FROM lesson_package_purchases WHERE lesson_package_purchases.lesson_package_type_id = 'test-drive-1') + (SELECT SUM(10) FROM lesson_package_purchases WHERE lesson_package_purchases.lesson_package_type_id = 'test-drive-2') + (SELECT SUM(10) FROM lesson_package_purchases WHERE lesson_package_purchases.lesson_package_type_id = 'test-drive')"
        td_users = "COUNT(td_purchases.id)"
        td4 = "COUNT(td4_purchases.id)"
        td2 = "COUNT(td2_purchases.id)"
        td1 = "COUNT(td1_purchases.id)"
        spend_td = "SELECT (CASE WHEN COUNT(td_purchases.id) = 0 THEN NULL ELSE avg(campaign_spends.spend) / COUNT(td_purchases.id) END)"
        purchases0 = "COUNT(CASE WHEN user_jamclass_purchases.purchases = 0 THEN 1 ELSE NULL END) / COUNT(user_jamclass_purchases.purchases)"
        purchases1 = "COUNT(CASE WHEN user_jamclass_purchases.purchases = 1 THEN 1 ELSE NULL END) / COUNT(user_jamclass_purchases.purchases)"
        purchases2 = "COUNT(CASE WHEN user_jamclass_purchases.purchases = 2 THEN 1 ELSE NULL END) / COUNT(user_jamclass_purchases.purchases)"
        purchases3 = "COUNT(CASE WHEN user_jamclass_purchases.purchases = 3 THEN 1 ELSE NULL END) / COUNT(user_jamclass_purchases.purchases)"
        purchases_rest = "COUNT(CASE WHEN user_jamclass_purchases.purchases >= 3 THEN 1 ELSE NULL END) / COUNT(user_jamclass_purchases.purchases)"
        purchases0_count = "COUNT(CASE WHEN user_jamclass_purchases.purchases = 0 THEN 1 ELSE NULL END)"
        purchases1_count = "COUNT(CASE WHEN user_jamclass_purchases.purchases = 1 THEN 1 ELSE NULL END)"
        purchases2_count = "COUNT(CASE WHEN user_jamclass_purchases.purchases = 2 THEN 1 ELSE NULL END)"
        purchases3_count = "COUNT(CASE WHEN user_jamclass_purchases.purchases = 3 THEN 1 ELSE NULL END)"
        purchases_rest_count = "COUNT(CASE WHEN user_jamclass_purchases.purchases >= 3 THEN 1 ELSE NULL END)"
        purchases_count = "COUNT(user_jamclass_purchases.purchases)"
        query =   User.select("date_trunc( 'month', users.created_at ) as cohort, origin_utm_campaign AS campaign, avg(campaign_spends.spend) as spend, count(users.id) AS registrations, (#{td_users}) as td_customers, (#{jamclass_revenue}) as jamclass_rev, (#{td4}) AS td4, (#{td2}) AS td2, (#{td1}) AS td1, (#{spend_td}) as spend_td, (#{purchases0}) as purchases0, (#{purchases1}) as purchases1, (#{purchases2}) as purchases2, (#{purchases3}) as purchases3, (#{purchases_rest}) as purchases_rest, (#{purchases0_count}) as purchases0_count, (#{purchases1_count}) as purchases1_count, (#{purchases2_count}) as purchases2_count, (#{purchases3_count}) as purchases3_count, (#{purchases_rest_count}) as purchases_rest_count, (#{purchases_count}) as purchases_count")
            .joins(%Q{
            LEFT OUTER JOIN
              campaign_spends
            ON
              campaign_spends.month = date_part('month', users.created_at) AND year = date_part('year', users.created_at) AND campaign_spends.campaign = users.origin_utm_campaign
            })
            .joins(%Q{
            LEFT OUTER JOIN
              lesson_package_purchases
            ON
              lesson_package_purchases.user_id = users.id
            })
            .joins(%Q{
            LEFT OUTER JOIN
              lesson_package_purchases AS td4_purchases
            ON
              lesson_package_purchases.user_id = users.id AND lesson_package_purchases.id = 'test-drive'
            })
            .joins(%Q{
            LEFT OUTER JOIN
              lesson_package_purchases AS td2_purchases
            ON
              lesson_package_purchases.user_id = users.id AND lesson_package_purchases.id = 'test-drive-2'
            })
            .joins(%Q{
            LEFT OUTER JOIN
              lesson_package_purchases AS td1_purchases
            ON
              lesson_package_purchases.user_id = users.id AND lesson_package_purchases.id = 'test-drive-1'
            })
            .joins(%Q{
            LEFT OUTER JOIN
              lesson_package_purchases AS td_purchases
            ON
              lesson_package_purchases.user_id = users.id AND lesson_package_purchases.id in ('test-drive', 'test-drive-2', 'test-drive-1')
            })
            .joins(%Q{
            INNER JOIN
              user_jamclass_purchases AS user_jamclass_purchases
            ON
              user_jamclass_purchases.user_id = users.id
            })
        .group('users.origin_utm_campaign, cohort')


        user_inserts = "INSERT INTO jam_class_reports (cohort, campaign, spend, registrations, td_customers, jamclass_rev, td4, td2, td1, spend_td, purchases0, purchases1, purchases2, purchases3, purchases_rest, purchases0_count, purchases1_count, purchases2_count, purchases3_count, purchases_rest_count, purchases_count) (#{query.to_sql})"
        User.connection.execute("DELETE FROM jam_class_reports")
        User.connection.execute(user_inserts)
        purchases0 = "SUM(jam_class_reports.purchases0_count) / SUM(jam_class_reports.purchases_count)"
        purchases1 = "SUM(jam_class_reports.purchases1_count) / SUM(jam_class_reports.purchases_count)"
        purchases2 = "SUM(jam_class_reports.purchases2_count) / SUM(jam_class_reports.purchases_count)"
        purchases3 = "SUM(jam_class_reports.purchases3_count) / SUM(jam_class_reports.purchases_count)"
        purchases_rest = "SUM(jam_class_reports.purchases_rest_count) / SUM(jam_class_reports.purchases_count)"

            group_inserts = "INSERT INTO jam_class_reports (cohort, campaign, spend, registrations, td_customers, jamclass_rev, td4, td2, td1, spend_td, purchases0, purchases1, purchases2, purchases3, purchases_rest)
          (SELECT NULL, jam_class_reports.campaign, SUM(spend), SUM(registrations), SUM(td_customers), SUM(jamclass_rev), SUM(td4), SUM(td2), SUM(td1), CASE WHEN SUM(td4) + SUM (td2) + SUM(td1) = 0 THEN NULL ELSE (SUM(spend) / (SUM(td4) + SUM (td2) + SUM(td1))) END,
          #{purchases0}, #{purchases1}, #{purchases2}, #{purchases3}, #{purchases_rest} FROM jam_class_reports
          GROUP BY campaign)"
        User.connection.execute(group_inserts)
        reports = JamClassReport.order('campaign, cohort DESC NULLS LAST')
        if campaign_filter
          reports = reports.where(campaign: campaign_filter)
        end

        reports
      end

    end

    def self.roll_forward_bookings
      LessonBooking.where('remaining_roll_forward_amount_in_cents > 0')
    end
  end
end