Consider calculating Trending Score on the database side

[davidyuk]

The current approach reduces the database normalization by adding a score column based on the data from other tables. Potentially, it can improve performance, but at the cost of extra code and data management.

While these computations can be delegated to the database, so we can rely on its internal optimizations.

SQL has enough features for that. This is how it can be implemented:

SELECT
    0.6 * COUNT(transactions.tx_hash) / <max>
      * 0.4 * SUM(transactions.volume) / <max>
      / LEAST(EXTRACT(EPOCH FROM current_timestamp - token.created_at::timestamp)::INTEGER, 60 * 60 * 24) / (60 * 60 * 24)
    as score,
    token.sale_address
FROM public.token
INNER JOIN public.transactions ON token.sale_address = transactions.sale_address
WHERE transactions.created_at > current_date - 1000
GROUP BY token.sale_address
ORDER BY score DESC

<max> can also be evaluated in the same query, but it takes a significant time for me to remember

found while checking #91

[davidyuk]

Here is what I got:

SELECT
    token.name,
    token.sale_address,
    COUNT(case when transactions.created_at > current_date - 50 then transactions.tx_hash end) as recentTxCount,
    SUM(case when transactions.created_at > current_date - 50 and transactions.tx_type = 'buy' then CAST(NULLIF(transactions.amount->>'ae', 'NaN') AS DECIMAL) else 0 end) as recentTxVolume,
    LEAST(EXTRACT(EPOCH FROM current_timestamp - token.created_at::timestamp)::INTEGER, 60 * 60 * 24 * 30) as age,

    0.6
        * COUNT(case when transactions.created_at > current_date - 50 then transactions.tx_hash end)
        / (MAX(COUNT(case when transactions.created_at > current_date - 50 then transactions.tx_hash end)) OVER ())
        + 0.3
        * SUM(case when transactions.created_at > current_date - 50 and transactions.tx_type = 'buy' then CAST(NULLIF(transactions.amount->>'ae', 'NaN') AS DECIMAL) else 0 end)
        / (MAX(SUM(case when transactions.created_at > current_date - 50 and transactions.tx_type = 'buy' then CAST(NULLIF(transactions.amount->>'ae', 'NaN') AS DECIMAL) else 0 end)) OVER ())
        + 0.1
        * (1 - LEAST(EXTRACT(EPOCH FROM current_timestamp - token.created_at::timestamp)::INTEGER, 60 * 60 * 24 * 30) / (60 * 60 * 24 * 30))
    as score
FROM token
INNER JOIN transactions ON token.sale_address = transactions.sale_address
GROUP BY token.sale_address
ORDER BY score DESC
LIMIT 50

evaluates to

name	sale_address	recenttxcount	recenttxvolume	age	score
EMOTER	ct_rxNfTJj7DKUK15vCsdFxBuNTkCRXYwJriRz5BA1AgpasgWEBP	186	1102.017348534261812008	2592000	0.61004132244403040872
IMAE	ct_2VRFBXEiwX3SCUpFsWudBCF8pKsjJVnHye3haVZpr28Ku95uvf	21	32924.468505323635317991	2490305	0.46774193548387096774
TROLL	ct_22BaXN3H59DuEFY9Rewk5heZXmuHTuN7sq79PQA5zMEMFx77R9	36	10171.886150129288605136	2592000	0.20881286235680617601
FREE-BACHIASHVILI	ct_26mHHCHmreLUmziXHvAvLYsQvzZZAUb7GzvfHFJ5X7riwcAeFZ	37	9437.833745654284962842	2592000	0.20535015624194002153
WORD-CRAFT	ct_wS3iFu3UkvprrkreRdjJJ8RrK9tLou5LeBBSbiPbmf6g51gvt	29	11476.322795186663929913	2592000	0.19811793657938190921
...

in 300 ms on a production database snapshot 🤷‍♀️

[davidyuk]

The above SELECT can be put into a Materialized View to improve performance. But needed to execute REFRESH MATERIALIZED VIEW <view name>; in cron after that. This refreshing still is quite cheap, so it can be done like every minute.