Add route type level stats

-- all onsights
    SELECT   t.USER_ID
            ,t.ROUTE_ID
            ,t.TICK_DATE
            ,t.TICK_TYPE
            ,r.ROUTE_NAME
            ,r.ROUTE_TYPE    
            ,r.RATING
            ,gm.Id
    FROM     TICKS t
        JOIN ROUTES r
        ON   t.ROUTE_ID = r.ROUTE_ID
        JOIN GRADEMAP gm
        ON   r.RATING = gm.YOSEMITE
    WHERE    t.TICK_TYPE = 'Onsight'
        AND  t.USER_ID = 106308715
    ORDER BY Id desc;

-- Onsight Levels
SELECT  GRADE_ID
        ,(OnsightCount / CAST(TotalCount AS REAL)) * 100 AS OnsightPercentage
        ,(RedpointCount / CAST(TotalCount AS REAL)) * 100 AS RedpointPercentage
FROM    (
    SELECT   GRADE_ID
             ,SUM(CASE WHEN TICK_TYPE = 'Onsight' THEN 1 ELSE 0 END) AS OnsightCount
             ,SUM(CASE WHEN TICK_TYPE = 'Redpoint' THEN 1 ELSE 0 END) AS RedpointCount
             ,SUM(CASE WHEN TICK_TYPE = 'Unknown' THEN 1 ELSE 0 END) AS UnknownCount
             ,SUM(CASE WHEN TICK_TYPE = 'Fell' THEN 1 ELSE 0 END) AS FellCount
             ,SUM(CASE WHEN TICK_TYPE = 'Follow' THEN 1 ELSE 0 END) AS FollowCount
             ,SUM(CASE WHEN TICK_TYPE = 'Toprope' THEN 1 ELSE 0 END) AS TopropeCount
             ,SUM(CASE WHEN TICK_TYPE = 'Solo' THEN 1 ELSE 0 END) AS SoloCount
             ,SUM(CASE WHEN TICK_TYPE = 'Pinkpoint' THEN 1 ELSE 0 END) AS PinkpointCount
             ,SUM(CASE WHEN TICK_TYPE = 'Flash' THEN 1 ELSE 0 END) AS FlashCount
             ,SUM(CASE WHEN TICK_TYPE = NULL THEN 1 ELSE 0 END) AS NullCount
             ,Count(*) AS TotalCount
    FROM (
        SELECT   r.RATING
                ,gm.GRADE_ID
                ,t.TICK_TYPE
        FROM     TICKS t
            JOIN ROUTES r
            ON   t.ROUTE_ID = r.ROUTE_ID
            JOIN GRADEMAP gm
            ON   r.RATING = gm.YOSEMITE
        WHERE    t.USER_ID = 106308715
            AND  r.ROUTE_TYPE = 'Trad'
            AND  t.TICK_TYPE <> 'Unknown'
            AND  t.TICK_DATE < datetime('now','-30 days') 
        ) tks
    GROUP BY GRADE_ID
    ) cnts;

-- distinct ticks
SELECT DISTINCT 
            USER_ID
            ,COUNT(*) AS TICK_COUNT
FROM        TICKS t
GROUP BY
            t.USER_ID;

-- total number of ticks
SELECT   USER_ID
        ,COUNT(*) AS TICK_COUNT
FROM    TICKS
GROUP BY
        USER_ID;

-- Number of Onsights
SELECT   GRADE_ID
         ,SUM(CASE WHEN TICK_TYPE = 'Onsight' THEN 1 ELSE 0 END) AS OnsightCount
         ,Count(*) AS TotalCount
FROM (
    SELECT   r.RATING
            ,gm.GRADE_ID
            ,t.TICK_TYPE
    FROM     TICKS t
        JOIN ROUTES r
        ON   t.ROUTE_ID = r.ROUTE_ID
        JOIN GRADEMAP gm
        ON   r.RATING = gm.YOSEMITE
    WHERE    t.USER_ID = 106308715
        AND  r.ROUTE_TYPE = 'Trad'
        AND  t.TICK_DATE < datetime('now','-90 days') 
    ) tks
GROUP BY GRADE_ID;

-- Number of Redpoints
SELECT   GRADE_ID
         ,SUM(CASE WHEN TICK_TYPE = 'Redpoint' THEN 1 ELSE 0 END) AS RedpointCount
         ,Count(*) AS TotalCount
FROM (
    SELECT   r.RATING
            ,gm.GRADE_ID
            ,t.TICK_TYPE
    FROM     TICKS t
        JOIN ROUTES r
        ON   t.ROUTE_ID = r.ROUTE_ID
        JOIN GRADEMAP gm
        ON   r.RATING = gm.YOSEMITE
    WHERE    t.USER_ID = 106308715
        AND  r.ROUTE_TYPE = 'Trad'
        AND  t.TICK_DATE < datetime('now','-90 days') 
    ) tks
GROUP BY GRADE_ID;

-- Grade distribution by Tick Type

SELECT    gm.GRADE_ID
          ,gm.YOSEMITe
          ,TICK_TYPE
          ,COUNT(*) AS TickCount
FROM      TICKS t
    JOIN  ROUTES r
     ON   t.ROUTE_ID = r.ROUTE_ID
    JOIN  GRADEMAP gm
     ON   r.RATING = gm.YOSEMITE
WHERE     USER_ID = 106308715
     AND  t.TICK_DATE < datetime('now','-90 days') 
GROUP BY  
          gm.GRADE_ID, gm.YOSEMITE, t.TICK_TYPE
ORDER BY
          gm.GRADE_ID asc;

-- Max Redpoint

SELECT    r.ROUTE_TYPE
          ,MAX(gm.GRADE_ID)
FROM      TICKS t
    JOIN  ROUTES r
      ON  t.ROUTE_ID = r.ROUTE_ID
    LEFT
    JOIN  GRADEMAP gm
      ON  gm.YOSEMITE = r.RATING
          OR gm.HUECO = r.RATING
          OR gm.EURO = r.RATING
          OR gm.FONT = r.RATING
          OR gm.WATERICE = r.RATING
WHERE     t.USER_ID = 106308715
GROUP BY  r.ROUTE_TYPE;

We may need to change the on clause to something like below to capture all grade denominations:

SELECT *
FROM      TICKS t
    JOIN  ROUTES r
      ON  t.ROUTE_ID = r.ROUTE_ID
    LEFT
    JOIN  GRADEMAP gm
      ON  gm.YOSEMITE = r.RATING
          OR gm.HUECO = r.RATING
          OR gm.EURO = r.RATING
          OR gm.FONT = r.RATING
          OR gm.WATERICE = r.RATING
WHERE    gm.GRADE_ID IS NULL;

teamrsm / analytic-spraydown

Add route type level stats #62