products = Product.arel_table
orders = Order.arel_table

average_purchase_time = Arel::Nodes::Case.new.when(orders[:finished_at].not_eq(nil))
                                         .then(orders[:finished_at] - orders[:created_at])

purchased = Arel::Nodes::Case.new.when(orders[:finished_at].not_eq(nil))
                             .then(1).else(0)

statement = products
            .outer_join(orders)
            .on(orders[:product_id].eq(products[:id]))
            .project(products[:id].count(true).as('total'))
            .project(purchased.sum.as('purchased'))
            .project(average_purchase_time.average.as('average_purchase_time'))

Product.find_by_sql(statement.to_sql)

Explaining:#

products = Product.arel_table
orders = Order.arel_table

When you call arel_table for an ActiveRecord class, this will return an Arel::Table object.

average_purchase_time = Arel::Nodes::Case.new.when(orders[:finished_at].not_eq(nil))
                                         .then(orders[:finished_at] - orders[:created_at])

purchased = Arel::Nodes::Case.new.when(orders[:finished_at].not_eq(nil))
                             .then(1).else(0)

With Arel::Nodes::Case, you can create an object that generates a CASE expression.

The method outer_join is used to left join a table, that returns an Arel::SelectManager object. Please remember to call the method on with the ON clause.

The method project is used to include some SELECT clauses. For our example, we are including the sum of all distinct products, the sum of purchased products, and the average of purchase time.

Finally, just run our SQL with Product.find_by_sql(statement.to_sql).