create_products.rb

class CreateProducts < ActiveRecord::Migration
  def change
    create_table :products do |t|
      t.string :name
      t.string :brand
    end
  end
end

product.rb

class Product < ActiveRecord::Base

  BRAND_CAPITIALIZATION_GROUPS_AND_COUNTS_COLUMNS = ["brand_for_grouping", "brand_to_display", "count_per_unique_brand_capitilization", "row_number" ,"total_count"]
  MOST_POPULAR_BRAND_CAPITIALIZATION_COLUMNS = ['brand_to_display', 'total_count', 'row_number']


  def self.seed
    flavors = ['Chocolate', 'Vanilla', 'Strawberry', 'Grape', 'Apple', 'Rocky Road', 'Orange']
    brands = ['Whole Foods', 'Safeway', 'Kroger', 'Schnucks']
    transforms = [:downcase, :upcase, :titleize]
  
    25.times {Product.create(:name => "#{flavors.shuffle.first} Ice Cream", :brand => brands.shuffle.first.send( transforms.shuffle.first))}
    8.times {Product.create(:name => "#{flavors.shuffle.first} Ice Cream", :brand => brands.shuffle.first.titleize)}
  end

  def self.query_to_md(columns, query)
    results = query.to_a
    puts columns.map(&:titleize).join('  |  ')
    puts columns.map{|x| x.gsub(/./, '-')}.join('  |  ')

    results.each do |row|
      puts columns.map{|col| row.send(col)}.join('  |  ')
    end
  end

  def self.table_data_md(search)
    query_to_md(["name", "brand"], Product.matching_search(search).order("lower(brand)"))
  end

  def self.brand_capitialization_groups_and_counts_md(search)
    query_to_md(
      BRAND_CAPITIALIZATION_GROUPS_AND_COUNTS_COLUMNS, 
      Product.brand_capitialization_groups_and_counts(search).order("brand_for_grouping").order("row_number")
    )
  end

  def self.brand_capitialization_groups_and_counts_interesting_md(search)
    query = "with " << brand_capitialization_groups_and_counts_cte(search) <<
                    " select " << BRAND_CAPITIALIZATION_GROUPS_AND_COUNTS_COLUMNS.join(',')  <<
                    " from brand_capitialization_groups_and_counts" <<
                    " where row_number = 1"

    query_to_md(BRAND_CAPITIALIZATION_GROUPS_AND_COUNTS_COLUMNS, Product.find_by_sql(query))
  end

  def self.most_popular_brand_capitialization_cte_md(search)
    query = "with " << brand_capitialization_groups_and_counts_cte(search) << ", " << most_popular_brand_capitialization_cte <<
                    " select " << MOST_POPULAR_BRAND_CAPITIALIZATION_COLUMNS.join(',')  <<
                    " from most_popular_brand_capitialization"

    query_to_md(MOST_POPULAR_BRAND_CAPITIALIZATION_COLUMNS, Product.find_by_sql(query))
  end

  def self.matching_brands_md(search)
    query_to_md(['brand_to_display', 'total_count'], Product.matching_brands('ice'))
  end


  scope :brand_capitialization_groups_and_counts, lambda { |search|
      Product.select("lower(coalesce(products.brand, '')) as brand_for_grouping, 
        coalesce(products.brand, '') as brand_to_display, 
        count(*) over (partition by coalesce(products.brand, '')) as count_per_unique_brand_capitilization,
        row_number() over (partition by coalesce(products.brand, '')) as row_number,
        count(*) over (partition by lower(coalesce(products.brand, ''))) as total_count").matching_search(search) 
  }

  def self.brand_capitialization_groups_and_counts_cte(search)
    "brand_capitialization_groups_and_counts as 
    (" << Product.brand_capitialization_groups_and_counts(search).to_sql << ")"
  end

  def self.most_popular_brand_capitialization_cte
    "most_popular_brand_capitialization as 
      (
        select 
          brand_to_display, 
          total_count,
          row_number() over (partition by brand_for_grouping order by count_per_unique_brand_capitilization desc) as row_number
        from brand_capitialization_groups_and_counts
        where row_number = 1
      )"
  end

  def self.matching_brands(search)
    query = "with " << brand_capitialization_groups_and_counts_cte(search) << "," << most_popular_brand_capitialization_cte << "
      select brand_to_display, total_count from most_popular_brand_capitialization 
      where row_number = 1
      order by total_count desc"

      Product.find_by_sql(query)
  end

  scope :matching_search, lambda {|search|
    where("name ilike ?", "%#{search}%")
  }
end