CodeSOD: Dashboard Confessional

Three years ago, this XKCD comic captured a lot of the problems we have with gathering requirements:

Our users have no idea which kinds of problems are hard and which kinds are easy. This isn't just for advanced machine learning classification projects- I've had users who assumed changing the color of an element on a page was hard (it wasn't), to users who assumed wiring up our in-house ERP to a purchased ERP was the simplest thing ever (it wasn't).

Which brings us to Christopher Shankland's contribution. He works for a game company, and while that often means doing game development, it often means doing tooling and platform management for the design team, like providing fancy dashboards for the designers to review how users play the game so that they can tweak the play.

That lead to this conversation:

Game Designer: I want to see how players progress through the game missions
Christopher: Great. I'll add a funnel chart to our dashboard app, which can query data from the database!
Game Designer: Also, I need to change the order the missions display in all the time"
Christopher: Okay, that'll require a data change every time you want to flip the order"
Game Designer: Fine, but I shouldn't have to ask anyone else to do it"
Christopher: Um" I'd have to bolt a UI onto the database, it's not really meant-
Game Designer: That sounds time consuming. I need this data YESTERDAY.
Christopher: I could-
Game Designer: YESTERDAY. GIVE ME DATA. NOW.

So Christopher hacked together a solution. Between fighting with the designer's fluid and every changing demands, the fact that what the designer wanted didn't mesh well with how the dashboard system assumed analytics would be run, the demand that it be done in the dashboard system anyway, and the unnecessary time pressure, Christopher didn't do his best work. He sends us this code, as penance. It's long, it's convoluted, and it uses lots of string concatenation to generate SQL statements.

As Chris rounded out his message to us: "This is why I drink."

-- Create syntax for 'chart_first_map_daily'DROP PROCEDURE IF EXISTS `chart_first_map_daily`;DELIMITER ;;CREATE DEFINER=`megaforce_stats`@`%` PROCEDURE `chart_first_map_daily`(IN timeline INT)BEGINSET SESSION group_concat_max_len = 1000000;DROP TABLE IF EXISTS `megaforce_stats`.`chart_first_map_daily`;CREATE TABLE `megaforce_stats`.`chart_first_map_daily` ( `absolute_order` INT(11) UNSIGNED NOT NULL, `date` DATE NOT NULL, `task_id` INT(11) UNSIGNED NOT NULL, `number_completed` INT(11) UNSIGNED NOT NULL DEFAULT 0, `new_user_completion_percentage` FLOAT(23) NOT NULL DEFAULT 0, `segment` VARCHAR(32) DEFAULT "Unknown", PRIMARY KEY (`date`, `task_id`, `segment`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;SET @last_date = date_sub(curdate(), INTERVAL 1 DAY);SET @timeline = timeline;SET @first_date = date_sub(@last_date, INTERVAL @timeline DAY);SET @first_campaign_id = (SELECT `id` FROM `megaforce_game`.`campaigns` WHERE NOT EXISTS (SELECT * FROM `megaforce_game`.`campaign_dependencies` WHERE `unlocked_campaign_id` = `megaforce_game`.`campaigns`.`id`) AND `active` = 1 AND `type_id` NOT IN (2,3,4));-- Create a helper table for orderingDROP TABLE IF EXISTS `megaforce_stats`.`absolute_task_ordering`;CREATE TABLE `megaforce_stats`.`absolute_task_ordering` ( `task_id` INT(11) UNSIGNED NOT NULL, `absolute_order` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (`absolute_order`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;SET @current_mission_id = -1;SET @sort_order = 2;SELECT IF(COUNT(`id`) > 0, `id`, -1) INTO @current_mission_idFROM `megaforce_game`.`missions`WHERE NOT EXISTS ( SELECT * FROM `megaforce_game`.`mission_dependencies` WHERE `unlocked_mission_id` = `megaforce_game`.`missions`.`id` ) AND active = 1 AND campaign_id = @first_campaign_id AND type_id = 1;WHILE @current_mission_id > 0 DO INSERT INTO `megaforce_stats`.`absolute_task_ordering` (`task_id`) SELECT `id` FROM `megaforce_game`.`tasks` WHERE `mission_id` = @current_mission_id AND `active` = 1 ORDER BY `order`; INSERT INTO `megaforce_stats`.`chart_first_map_daily` ( `absolute_order`,`date`,`task_id`, `number_completed`,`new_user_completion_percentage`, `segment` ) SELECT `task_info`.`absolute_order`, `sessions`.`date`, `task_info`.`task_id`, `task_info`.`number_completed`, `task_info`.`number_completed` / `sessions`.`new_users`, -1 FROM ( SELECT `date`, SUM(`new_users`) AS `new_users` FROM `megaforce_stats`.`sessions_daily` WHERE DATE(`date`) > @first_date AND DATE(`date`) <= @last_date GROUP BY `date` ) AS `sessions` LEFT JOIN ( SELECT `absolute_order`, DATE(`date_completed`) AS `date`, COUNT(DISTINCT(`user_name`)) AS `number_completed`, `megaforce_game`.`tasks`.`id` AS `task_id` FROM `megaforce_game`.`track_completed_tasks` JOIN `megaforce_stats`.`accounts_real` ON `user_name` = `userName` JOIN `megaforce_game`.`tasks` ON `megaforce_game`.`tasks`.`id` = `megaforce_game`.`track_completed_tasks`.`task_id` JOIN `megaforce_stats`.`absolute_task_ordering` ON `megaforce_stats`.`absolute_task_ordering`.`task_id` = `megaforce_game`.`tasks`.`id` WHERE DATE(`date_completed`) = DATE(`date_created`) AND `mission_id` = @current_mission_id AND `active` = 1 GROUP BY DATE(`date_completed`), `megaforce_game`.`tasks`.`id` ORDER BY `order` ) AS `task_info` ON `task_info`.`date` = `sessions`.`date`; -- Create our CREATE TABLE statement SET @mission_chart_table_name = CONCAT("chart_first_map_daily_", @current_mission_id); SELECT GROUP_CONCAT(`id` SEPARATOR "_completion` INT(11) UNSIGNED NOT NULL, `task_") INTO @mission_chart_task_columns FROM `megaforce_game`.`tasks` WHERE `mission_id` = @current_mission_id AND `active` = 1 ORDER BY `order`; SET @drop_mission_chart = CONCAT("DROP TABLE IF EXISTS `megaforce_stats`.`", @mission_chart_table_name, "`"); PREPARE stmt FROM @drop_mission_chart; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @create_mission_chart = CONCAT(" CREATE TABLE `megaforce_stats`.`", @mission_chart_table_name, "` ( `date` DATE NOT NULL, `task_", @mission_chart_task_columns, "_completion` INT(11) UNSIGNED NOT NULL, `segment` VARCHAR(32) DEFAULT 'Unknown', PRIMARY KEY (`date`,`segment`) ) ENGINE = InnoDB DEFAULT CHARSET=utf8 "); PREPARE stmt FROM @create_mission_chart; EXECUTE stmt; DEALLOCATE PREPARE stmt; SELECT GROUP_CONCAT(`id` SEPARATOR "_completion`.`number_completed` / `sessions`.`new_users` * 100, `task_") INTO @task_list FROM `megaforce_game`.`tasks` WHERE `mission_id` = @current_mission_id AND `active` = 1 ORDER BY `order`; SELECT GROUP_CONCAT( CONCAT(`id`, " GROUP BY DATE(`date_completed`), `segment`) AS `task_", `id`, "_completion` ON `task_", `id`, "_completion`.`segment` = `sessions`.`segment` AND `task_", `id`) SEPARATOR "_completion`.`date` = `sessions`.`date` LEFT JOIN ( SELECT DATE(`date_completed`) AS `date`, COUNT(*) AS `number_completed`, `segment` FROM `megaforce_game`.`track_completed_tasks` JOIN `megaforce_stats`.`accounts_real` ON `track_completed_tasks`.`user_name` = `accounts_real`.`userName` WHERE DATE(`date_created`) = DATE(`date_completed`) AND `task_id` = " ) INTO @task_join_tables FROM `megaforce_game`.`tasks` WHERE `mission_id` = @current_mission_id AND `active` = 1 ORDER BY `order`; SET @insert_mission_chart = CONCAT(" INSERT INTO `megaforce_stats`.`", @mission_chart_table_name, "` SELECT `sessions`.`date`,`task_", @task_list, "_completion`.`number_completed` / `sessions`.`new_users` * 100, `sessions`.`segment` FROM ( SELECT `date`, `new_users`, `segment` FROM `megaforce_stats`.`sessions_daily` WHERE DATE(`date`) > @first_date AND DATE(`date`) <= @last_date GROUP BY `date`, `segment` ) AS `sessions` LEFT JOIN ( SELECT DATE(`date_completed`) AS `date`, COUNT(*) AS `number_completed`, `segment` FROM `megaforce_game`.`track_completed_tasks` JOIN `megaforce_stats`.`accounts_real` ON `track_completed_tasks`.`user_name` = `accounts_real`.`userName` WHERE DATE(`date_created`) = DATE(`date_completed`) AND `task_id` = ", @task_join_tables, "_completion`.`date` = `sessions`.`date` "); PREPARE stmt FROM @insert_mission_chart; EXECUTE stmt; DEALLOCATE PREPARE stmt; SELECT GROUP_CONCAT( CONCAT(`id`, " GROUP BY DATE(`date_completed`)) AS `task_", `id`, "_completion` ON `task_", `id`) SEPARATOR "_completion`.`date` = `sessions`.`date` LEFT JOIN ( SELECT DATE(`date_completed`) AS `date`, COUNT(*) AS `number_completed` FROM `megaforce_game`.`track_completed_tasks` JOIN `megaforce_stats`.`accounts_real` ON `track_completed_tasks`.`user_name` = `accounts_real`.`userName` WHERE DATE(`date_created`) = DATE(`date_completed`) AND `task_id` = " ) INTO @task_join_tables FROM `megaforce_game`.`tasks` WHERE `mission_id` = @current_mission_id AND `active` = 1 ORDER BY `order`; SET @insert_mission_chart = CONCAT(" INSERT INTO `megaforce_stats`.`", @mission_chart_table_name, "` SELECT `sessions`.`date`,`task_", @task_list, "_completion`.`number_completed` / `sessions`.`new_users` * 100, -1 FROM ( SELECT `date`, SUM(`new_users`) AS `new_users` FROM `megaforce_stats`.`sessions_daily` WHERE DATE(`date`) > @first_date AND DATE(`date`) <= @last_date GROUP BY `date` ) AS `sessions` LEFT JOIN ( SELECT DATE(`date_completed`) AS `date`, COUNT(*) AS `number_completed` FROM `megaforce_game`.`track_completed_tasks` JOIN `megaforce_stats`.`accounts_real` ON `track_completed_tasks`.`user_name` = `accounts_real`.`userName` WHERE DATE(`date_created`) = DATE(`date_completed`) AND `task_id` = ", @task_join_tables, "_completion`.`date` = `sessions`.`date` "); PREPARE stmt FROM @insert_mission_chart; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- Dynamically create our charts (multiple data by mission) DELETE FROM `megaforce_stats`.`gecko_chart_sql` WHERE `sql_key` = CONCAT("CHART_FIRST_MAP_DAILY_", @current_mission_id); DELETE FROM `megaforce_stats`.`gecko_chart_info` WHERE `sql_key` = CONCAT("CHART_FIRST_MAP_DAILY_", @current_mission_id); INSERT INTO `megaforce_stats`.`gecko_chart_sql` (`sql_key`,`sql_query`,`data_field`,`segment_field`) VALUES (CONCAT("CHART_FIRST_MAP_DAILY_", @current_mission_id), CONCAT("SELECT * FROM `megaforce_stats`.`", @mission_chart_table_name, "`"), "date", "segment"); INSERT INTO `megaforce_stats`.`gecko_chart_info` (`sql_key`,`data_field`,`title`,`category`,`sort_order`,`type`,`data_name`,`chart_type`) VALUES (CONCAT("CHART_FIRST_MAP_DAILY_", @current_mission_id), "", CONCAT("Mission ", @current_mission_id, " Task Completion"), 10, @sort_order, "spline", "", "hc_line_multiple_segments_date"); SET @sort_order = @sort_order + 1; SELECT IF(COUNT(`unlocked_mission_id`) > 0, `unlocked_mission_id`, -1) INTO @current_mission_id FROM `megaforce_game`.`mission_dependencies` WHERE `required_mission_id` = @current_mission_id;END WHILE;END;;DELIMITER ;

[Advertisement] Application Release Automation for DevOps - integrating with best of breed development tools. Free for teams with up to 5 users. Download and learn more today!