count of results per day of last week

count.sql

SELECT 
    EXTRACT(DAY FROM created_at) AS the_day, 
    COUNT(created_at) AS count_all 
FROM "results" 
WHERE "results"."result_type" = 'outcome' 
AND (DATE("results"."created_at") BETWEEN '2011-11-21' AND '2011-12-01') 
GROUP BY created_at 
ORDER BY created_at

scope.rb

scope :recent, lambda {
  select(['EXTRACT(DAY FROM created_at) AS the_day', 'COUNT(created_at) AS count_all'])
  .where("DATE(created_at) BETWEEN ? AND ? ", Date.today.prev_week, Date.today)
  .group(:created_at)
  .order(:created_at)
}