search

rrabinovitch / rails_engine_rr

ReST API for sales data
0 stars 0 forks source link

Business intelligence: Merchants with Most Revenue endpoint #9

Closed rrabinovitch closed 4 years ago

rrabinovitch commented 4 years ago

This endpoint should return a variable number of merchants ranked by total revenue.

The URI should follow this pattern: GET /api/v1/merchants/most_revenue?quantity=x

where x is the number of merchants to be returned.

NOTE: Only paid invoices should be counted in revenue totals or statistics. A paid invoice has at least one successful transaction.

rrabinovitch commented 4 years ago 
SELECT merchants.*, SUM(invoice_items.unit_price * invoice_items.quantity) AS revenue
FROM merchants
JOIN invoices ON merchants.id=invoices.merchant_id
JOIN transactions ON transactions.invoice_id=invoices.id
JOIN invoice_items ON invoice_items.invoice_id=invoices.id
WHERE transactions.result='success' AND invoices.status='shipped'
GROUP BY merchants.id
ORDER BY revenue desc
LIMIT amt;
rrabinovitch commented 4 years ago

original AR query - passed my spec but didn't pass spec harness

Merchant.joins(:transactions, :invoice_items)
      .select("merchants.*, SUM(invoice_items.unit_price * invoice_items.quantity) AS revenue")
      .where("transactions.result='success' AND invoices.status='shipped'")
      .group(:id)
      .order("revenue desc")
      .limit(quantity)