sorting_recommendation.sql

-- Many-to-many table schema for users and favorites

CREATE TABLE `user_favorites` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `deal_id` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;

-- Run this as many times as you want to generate 100 users and 5 deals

INSERT INTO user_favorites (user_id, deal_id)
  SELECT 
    (FLOOR ( 1 + RAND() * 100 )) user_id, 
    (FLOOR ( 1 + RAND() * 5 )) deal_id;

-- Simple aggregation query to refresh results

SELECT 
  deal_id, COUNT(1) num_faves
FROM user_favorites
WHERE DATE(created_date) = CURDATE()
GROUP BY deal_id
ORDER BY num_faves DESC;