CREATE TABLE `userdata` (
  `id` bigint(20) unsigned NOT NULL,
  `timestamp` datetime NOT NULL,
  `titleid` int(10) unsigned default NULL,
  `version` tinyint(3) unsigned default NULL,
  `verrprt` tinyint(3) unsigned default NULL,
  `dlcmask` bigint(20) unsigned default NULL,
  `xuid` bigint(20) unsigned default NULL,
  `gamertag` varchar(16) default NULL,
  `lv` tinyint(3) unsigned default NULL,
  `xrgn` smallint(5) unsigned default NULL,
  `xlng` tinyint(3) unsigned default NULL,
  `xloc` tinyint(3) unsigned default NULL,
  `scrw` smallint(5) unsigned default NULL,
  `scrh` smallint(5) unsigned default NULL,
  `vidi` tinyint(3) unsigned default NULL,
  `vidw` tinyint(3) unsigned default NULL,
  `vidh` tinyint(3) unsigned default NULL,
  `vids` tinyint(3) unsigned default NULL,
  `scrr` float default NULL,
  `snd` tinyint(3) unsigned default NULL,
  `joy` tinyint(3) unsigned default NULL,
  `urgn` tinyint(3) unsigned default NULL,
  `uach` int(10) unsigned default NULL,
  `uzon` tinyint(3) unsigned default NULL,
  `ucrd` int(10) unsigned default NULL,
  `utit` int(10) unsigned default NULL,
  `udif` tinyint(3) unsigned default NULL,
  `usns` tinyint(3) unsigned default NULL,
  `uyax` tinyint(3) unsigned default NULL,
  `utia` tinyint(3) unsigned default NULL,
  `utic` int(10) unsigned default NULL,
  `dcpgmi` int(10) unsigned default NULL,
  `dcpgme` int(10) unsigned default NULL,
  `dcpgbu` int(10) unsigned default NULL,
  `dcbwup` int(10) unsigned default NULL,
  `dcbwdn` int(10) unsigned default NULL,
  `dcid` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`id`,`dcid`),
  KEY `timestamp` (`timestamp`),
  KEY `titleid` (`titleid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



CREATE TABLE `uniqueusers` (
  `id` bigint(20) unsigned NOT NULL,
  `timestamp` datetime NOT NULL,
  `titleid` int(10) unsigned default NULL,
  `version` tinyint(3) unsigned default NULL,
  `verrprt` tinyint(3) unsigned default NULL,
  `dlcmask` bigint(20) unsigned default NULL,
  `xuid` bigint(20) unsigned default NULL,
  `numrecords` bigint(20) unsigned default 0,
  `lv` tinyint(3) unsigned default NULL,
  `xrgn` smallint(5) unsigned default NULL,
  `xlng` tinyint(3) unsigned default NULL,
  `xloc` tinyint(3) unsigned default NULL,
  `urgn` tinyint(3) unsigned default NULL,
  `uach` int(10) unsigned default NULL,
  `ucrd` int(10) unsigned default NULL,
  `utit` int(10) unsigned default NULL,
  `utia` tinyint(3) unsigned default NULL,
  `utic` int(10) unsigned default NULL,
  `dcpgmi` float unsigned default NULL,
  `dcpgme` float unsigned default NULL,
  `dcbwup` float unsigned default NULL,
  `dcbwdn` float unsigned default NULL,
  PRIMARY KEY  (`xuid`,`titleid`),
  KEY `timestamp` (`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `uniqueusers` (
  `id`,
  `timestamp`,
  `titleid`,
  `version`,
  `verrprt`,
  `dlcmask`,
  `xuid`,
  `numrecords`,
  `lv`,
  `xrgn`,
  `xlng`,
  `xloc`,
  `urgn`,
  `uach`,
  `ucrd`,
  `utit`,
  `utia`,
  `utic`,
  `dcpgmi`,
  `dcpgme`,
  `dcbwup`,
  `dcbwdn`
  )
SELECT
  MIN(T1.id),
  MIN(T1.timestamp),
  MAX(T1.titleid),
  MAX(T1.version),
  MAX(T1.verrprt),
  BIT_OR(T1.dlcmask),
  T1.xuid,
  COUNT(T1.id),
  MAX(T1.lv),
  MAX(T1.xrgn),
  MAX(T1.xlng),
  MAX(T1.xloc),
  MAX(T1.urgn),
  MIN(T1.uach),
  MIN(T1.ucrd),
  MIN(T1.utit),
  MAX(T1.utia),
  MAX(T1.utic),
  AVG(T1.dcpgmi),
  AVG(T1.dcpgme),
  AVG(T1.dcbwup),
  AVG(T1.dcbwdn)
FROM
  userdata AS T1
  WHERE T1.titleid = 1161922774
  GROUP by T1.xuid;


  