Forums
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)
BEGIN
SET 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 ordering
DROP 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_id
FROM
`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]
BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!