Awesome snippets

[Proteusiq]

from typing import Callable

def dictionary_filter(*, blob:dict, callback:Callable) -> dict:
    """
    callable is a function with key, value arguments
    example: `dictionary_filter(blob=ex, callback=lambda key,value: key in ["hello",])`
    """

    result = {}
    for key, value in blob.items():
        if callback(key, value):
            result[key] = value
    return result

ex = {"hello": 1,
 "world": 2,
 "name": 4
}

dictionary_filter(blob=ex, callback=lambda key,value: value > 1)

[Proteusiq]

SQL

WITH player_performance AS (
    SELECT 
        "match_date",
        "position",
        "player_id",
        "predicted_score",
        "game_id",
        recorded_at AS recorded_time,
        TO_TIMESTAMP(CONCAT("match_date", "match_time"), 'YYYY-MM-DDHH24:MI:SS') AS prediction_time
    FROM game_data
    JOIN score_predictions ON (game_data.id = "game_id")
    WHERE 
        "target_metric" = 'player_id' 
), filtered_performance AS (
    SELECT 
        "match_date",
        "game_id",
        "position",
        "player_id",
        "predicted_score",
        recorded_time,
        MAX(prediction_time) AS prediction_time
    FROM player_performance
    WHERE recorded_time > prediction_time
    GROUP BY "match_date", "position", "player_id", "predicted_score", "game_id", recorded_time
)
SELECT 
    "match_date",
    COUNT("game_id") FILTER (WHERE "position" = 0 AND "player_id" = "predicted_score") AS correct_predictions,
    COUNT("game_id") FILTER (WHERE "player_id" = "predicted_score") AS top_predictions,
    COUNT("game_id") FILTER (WHERE "position" = 0) AS total_predictions,
    CAST(COUNT("game_id") FILTER (WHERE "position" = 0 AND "player_id" = "predicted_score") AS FLOAT) / COUNT("game_id") FILTER (WHERE "position" = 0) AS accuracy,
    CAST(COUNT("game_id") FILTER (WHERE "player_id" = "predicted_score") AS FLOAT) / COUNT("game_id") FILTER (WHERE "position" = 0) AS top_accuracy
FROM filtered_performance
GROUP BY "match_date"
ORDER BY "match_date" DESC;