users_hobbies.sql

-- юзеры
CREATE TABLE `users` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`firstname` VARCHAR(32) NULL DEFAULT 'No_name',
	PRIMARY KEY (`id`)
)
ENGINE=InnoDB
;

-- хобби
CREATE TABLE `hobbies` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`title` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `title` (`title`)
)
ENGINE=InnoDB
;


-- юзеры → хобби
CREATE TABLE `users_hobbies` (
	`user_id` INT(10) UNSIGNED NOT NULL,
	`hobby_id` SMALLINT(5) UNSIGNED NOT NULL,
	UNIQUE INDEX `users_hobbies` (`user_id`, `hobby_id`)
)
ENGINE=InnoDB
;

-- топ хобби
select 
	uh.hobby_id, 
	h.title 'hobby_title', 
	count(uh.hobby_id) `cnt` 
from 
	users_hobbies uh
left join 
	hobbies h on uh.hobby_id = h.id
group by 
	uh.hobby_id
order by
	`cnt` desc