Rails Engine

rails_engine.rb

require 'active_record'

ActiveRecord::Base.establish_connection adapter: 'sqlite3', database: ':memory:'

ActiveRecord::Schema.define do
  self.verbose = false

  create_table "invoice_items" do |t|
    t.integer  "invoice_id"
    t.integer  "quantity"
    t.integer  "unit_price"
  end

  create_table "invoices" do |t|
    t.integer  "merchant_id"
  end

  create_table "merchants" do |t|
    t.string   "name"
  end

  create_table "transactions" do |t|
    t.integer  "invoice_id"
    t.string   "result"
  end
  
  execute <<-SQL
    create view "revenue_by_invoice"
    as select sum(quantity*unit_price) as revenue,
              invoice_id
    from invoice_items
    group by invoice_id;
  SQL
  
  execute <<-SQL
    create view "successful_invoices"
    as select invoices.id          as invoice_id,
              invoices.merchant_id as merchant_id,
              revenue
    from invoices
    inner join revenue_by_invoice on id = invoice_id
    where exists (
      select 1 from "transactions"
      where "result" = "success"
      and   "invoice_id" = invoices.id
    );
  SQL
end

class Transaction < ActiveRecord::Base
  belongs_to :invoice
end

class InvoiceItem < ActiveRecord::Base
  belongs_to :invoice
end

class Invoice < ActiveRecord::Base
  has_many :transactions
  has_many :invoice_items
  belongs_to :merchant
end

class Merchant < ActiveRecord::Base
  has_many :invoices

  def self.most_revenue(num_merchants)
    merchants = Merchant.find_by_sql [<<-SQL, num_merchants]
      select *, coalesce( (select sum(revenue)
                           from successful_invoices
                           where successful_invoices.merchant_id = merchants.id
                          ),
                          (select 0)
                        )
                as revenue
      from merchants
      order by revenue desc
      limit ?
    SQL
  end
end



Invoice.create! do |invoice|
  invoice.build_merchant name: '$0 b/c invoice is unsuccessful'
  invoice.transactions.build result: "fail"
  invoice.invoice_items.build quantity: 10000, unit_price: 100000
end

Invoice.create! do |invoice|
  invoice.build_merchant name: '$1000 fewer items but more revenue'
  invoice.transactions.build result: "success"
  invoice.invoice_items.build quantity: 10, unit_price: 100
end

Invoice.create! do |invoice|
  invoice.build_merchant name: '$200 more items but less revenue'
  invoice.transactions.build result: "success"
  invoice.invoice_items.build quantity: 10, unit_price: 10
  invoice.invoice_items.build quantity: 10, unit_price: 10
end

Invoice.create! do |invoice|
  invoice.build_merchant name: '$210 has 2 successful transactions'
  invoice.transactions.build result: "success"
  invoice.transactions.build result: "success"
  invoice.invoice_items.build quantity: 10, unit_price: 11
  invoice.invoice_items.build quantity: 10, unit_price: 10
end

Merchant.create! do |merchant|
  merchant.name = '$205 more invoices, between the other two'
  merchant.invoices.build do |invoice|
    invoice.transactions.build result: "success"
    invoice.invoice_items.build quantity: 200, unit_price: 1
  end
  merchant.invoices.build do |invoice|
    invoice.transactions.build result: "success"
    invoice.invoice_items.build quantity: 1, unit_price: 5
  end
end


Merchant.most_revenue(5).map { |m| [m.revenue, m.name] }
# => [[1000, "$1000 fewer items but more revenue"],
#     [210, "$210 has 2 successful transactions"],
#     [205, "$205 more invoices, between the other two"],
#     [200, "$200 more items but less revenue"],
#     [0, "$0 b/c invoice is unsuccessful"]]

Merchant.most_revenue(2).map(&:name)
# => ["$1000 fewer items but more revenue",
#     "$210 has 2 successful transactions"]