some hot items SQL

hot-items-queries.sql

-- ratio with average view count log-trend
SELECT i.id FROM items i
LEFT JOIN global_stats vc ON vc.entry_type=510 AND vc.entry_id = i.id
INNER JOIN user_settings s ON s.user_id = i.user_id
WHERE s.is_publish_photos_agreed = 1 AND is_visible = 1 AND is_replicated = 0 AND is_reserved = 0
AND boutique_id IS NULL
AND created_at >= '2012-10-27'
AND i.catalog_id IN (%cat4%)
AND vc.value / (LN(DATEDIFF(NOW(),created_at)+1)*15.5+12.4) >= 0.8
ORDER BY id DESC

-- with favourites filter
SELECT i.id FROM items i
LEFT JOIN global_stats vc ON vc.entry_type=510 AND vc.entry_id = i.id
LEFT JOIN global_stats fc ON fc.entry_type=511 AND fc.entry_id = i.id
INNER JOIN user_settings s ON s.user_id = i.user_id
WHERE s.is_publish_photos_agreed = 1 AND is_visible = 1 AND is_replicated = 0 AND is_reserved = 0
AND boutique_id IS NULL
AND created_at >= '2012-10-27'
AND i.catalog_id IN (%cat4%)
AND vc.value / (LN(DATEDIFF(NOW(),created_at)+1)*15.5+12.4) >= 0.8
AND fc.value >= 1
ORDER BY id DESC

-- final formula
SELECT i.id FROM items i
LEFT JOIN global_stats vc ON vc.entry_type=510 AND vc.entry_id = i.id
LEFT JOIN global_stats fc ON fc.entry_type=511 AND fc.entry_id = i.id
INNER JOIN user_settings s ON s.user_id = i.user_id
WHERE s.is_publish_photos_agreed = 1 AND is_visible = 1 AND is_replicated = 0 AND is_reserved = 0
AND boutique_id IS NULL
AND catalog_id IN (4, 17, 21, 163, 164, 165, 553, 166, 167, 168, 162, 19, 156, 158, 552, 159, 160, 161, 155, 22, 20, 89, 236, 237, 238, 239, 240, 26, 88, 230, 231, 232, 569, 233, 234, 235, 90, 10, 177, 178, 179, 180, 181, 182, 176, 12, 221, 222, 14, 223, 224, 225, 226, 534, 227, 228, 11, 198, 199, 200, 539, 540, 541, 542, 201, 13, 190, 191, 192, 193, 194, 195, 196, 567, 197, 9, 183, 184, 185, 186, 187, 525, 526, 527, 189, 16, 543, 206, 207, 544, 208, 209, 210, 211, 213, 545, 547, 548, 15, 202, 203, 538, 204, 205, 7, 170, 171, 172, 174, 175, 555, 169, 8, 532, 530, 531, 533)
AND created_at >= '2012-10-15'
AND created_at <= '2012-10-31'
AND boutique_id IS NULL
AND vc.value / (LN(DATEDIFF(NOW(),created_at)+1)*15.5+12.4) >= 0.5
AND fc.value <= 5
ORDER BY rand()