Advanced Usage

Advanced Usage

If the supported screens do not provide enough functionality for you and you are familiar with SQL, you can connect to the database directly and run queries against it.

By default, the database is stored in the following location:

%localappdata%/SmbExplorerCompanion/SmbExplorerCompanion.db

And the database has the following schema:

For instance, if you want to select a list of batters who have had 30-30 seasons, you could run the following query:

SELECT p.Id,
       P.FirstName || ' ' || P.LastName AS PlayerName,
       S.Number                         AS SeasonNumber,
       PSBS.HomeRuns,
       PSBS.StolenBases,
       psgs.Power,
       psgs.Contact,
       psgs.Speed
FROM PlayerSeasonBattingStats PSBS
         JOIN main.PlayerSeasons PS ON PS.Id = PSBS.PlayerSeasonId
         JOIN main.Players P ON PS.PlayerId = P.Id
         JOIN Seasons S ON PS.SeasonId = S.Id
         JOIN main.PlayerSeasonGameStats PSGS ON PS.Id = PSGS.PlayerSeasonId
WHERE PSBS.IsRegularSeason = 1
  AND PSBS.HomeRuns > 30
  AND PSBS.StolenBases > 30
ORDER BY PSBS.HomeRuns DESC,
         PSBS.StolenBases DESC;

Or (another fun one), if you want to select a list of the top position players who have pitched at least 1/3 of an inning, you could run the following query:

SELECT p.Id,
       P.FirstName || ' ' || P.LastName AS PlayerName,
       SUM(PSPS.InningsPitched)         AS InningsPitched,
       SUM(PSPS.Wins)                   AS Wins,
       SUM(PSPS.Losses)                 AS Losses,
       AVG(PSPS.EarnedRunAverage)       AS EarnedRunAverage
FROM PlayerSeasonPitchingStats PSPS
         JOIN main.PlayerSeasons PS ON PS.Id = PSPS.PlayerSeasonId
         JOIN main.Players P ON P.Id = PS.PlayerId
         JOIN main.Seasons S ON PS.SeasonId = S.Id
WHERE P.PitcherRoleId IS NULL
GROUP BY p.Id, PlayerName
ORDER BY InningsPitched DESC;

If you wanted to view the league average OPS for position players and ERA for pitchers by season, you could run the following query:

WITH OpsRankings AS (SELECT S.Number                                  AS SeasonNumber,
                            AVG(PSBS.Ops)                             AS AverageOPS,
                            RANK() OVER (ORDER BY AVG(PSBS.Ops) DESC) AS Rank
                     FROM PlayerSeasonBattingStats PSBS
                              JOIN PlayerSeasons PS ON PSBS.PlayerSeasonId = PS.Id
                              JOIN Seasons S ON PS.SeasonId = S.Id
                              JOIN Players P ON PS.PlayerId = P.Id
                     WHERE PSBS.IsRegularSeason = 1
                       AND ABS(PSBS.OpsPlus - 100) <= 0.5
                       AND P.PitcherRoleId IS NULL
                     GROUP BY S.Number, PS.SeasonId),
     EraRankings AS (SELECT S.Number                                          AS SeasonNumber,
                            AVG(PSPS.EarnedRunAverage)                        AS AverageEra,
                            RANK() OVER (ORDER BY AVG(PSPS.EarnedRunAverage)) AS Rank
                     FROM PlayerSeasonPitchingStats PSPS
                              JOIN PlayerSeasons PS ON PSPS.PlayerSeasonId = PS.Id
                              JOIN Seasons S ON PS.SeasonId = S.Id
                              JOIN Players P ON PS.PlayerId = P.Id
                     WHERE PSPS.IsRegularSeason = 1
                       AND ABS(PSPS.EraMinus - 100) <= 0.5
                       AND P.PitcherRoleId IS NOT NULL
                     GROUP BY S.Number, PS.SeasonId)
SELECT ORS.SeasonNumber,
       ORS.AverageOPS,
       ORS.Rank      AS OpsRank,
       ER.AverageEra AS AverageERA,
       ER.Rank       AS EraRank
FROM OpsRankings ORS
         JOIN EraRankings ER ON ORS.SeasonNumber = ER.SeasonNumber
ORDER BY ORS.SeasonNumber;

For more examples, see the CompanionScripts repo.