Query SQL con los participantes del Hivepowerbday | Python + SQL + HiveSQL

¡Hola, Hivers!

Luego de conversar con @victoriabsb y ella plantearme la necesidad de que no quería sacar la tabla a mano relacionada con la iniciativa de "hivepowerbday", me pidió el favor de hacerlo de manera automatizada y acepté el reto, porque ya tengo experiencia extrayendo data de Hive vía Hive SQL

Sobre Hive SQL

Ella me dijo, quiero una tabla con lo siguiente:

Participantes de la iniciativa que tenga menos de 25k de HP y mas de 39 en Reputación, pero que hicieron Power UP durante la fecha de la iniciativa.

Vamos a la obra:

Inicié realizando un Query para comprobar que estoy usando de manera correcta el lenguaje y buscando los siguientes resultados

Autor de post, permlink, reputación y los vests

SELECT Comments.author, Comments.permlink, Accounts.reputation_ui, Accounts.vesting_shares
FROM comments (NOLOCK), Accounts
 
 
 
where Comments.depth = 0 and comments.created >= CONVERT(DATE,'2023-03-20') and comments.created < CONVERT(DATE,'2023-03-21') 

and Comments.author = Accounts.name

order by comments.created DESC

Luego procedí a ajustar más el Query para acercarme al resultado final que quiere @victoriabsb, entonces debo filtrar de esos post las personas que ese día publicaron y usaron el tag de la iniciativa.

El tag es: hivepowerbday

¿Cómo puedo hago eso? - Extrayendo con Tags

En Hive los tags se almacenan el JSON_METADATA de la publicación, así debo aplicar el filtro basado en esa información, pero usaré la tabla tags para que sea más fácil , como muestro a continuación:

SELECT Comments.author, Comments.permlink, Accounts.reputation_ui, Accounts.vesting_shares


FROM
    Tags
    INNER JOIN Comments ON Tags.comment_id = Comments.ID
    INNER JOIN Accounts ON Comments.author = Accounts.name


 
 
where Comments.depth = 0 and comments.created >= CONVERT(DATE,'2023-03-20') and comments.created < CONVERT(DATE,'2023-03-21') 


and Tags.tag = 'hivepowerbday'


order by comments.created DESC

Lo otro que me pidió victoria es que la hora pueda ajustar a la zona horaria de muchos países, así que voy a usar lo siguiente:

where Comments.depth = 0 and (comments.created AT TIME ZONE 'UTC') >= '2023-03-20 00:00:00' and (comments.created AT TIME ZONE 'UTC') <= '2023-03-21 06:00:00

Donde indico que la zona horaria sea UTC y la fecha final tenga 6 horas extras para incluir post que no publicaron al tiempo de UTC - 0

Ahora vamos con la fase final que es filtrar los usuarios que tienen reputación mayor a 39 y HP menor a 25k HP

Lo primero es convertir vests a Hive Power, para ello uso la tabla de DynamicGlobalProperties y utilizar el dato de hive_per_vest

SELECT 
    Comments.author, Comments.permlink, Accounts.reputation_ui, 
    (Accounts.vesting_shares * DynamicGlobalProperties.hive_per_vest) as hive_power



FROM
    Tags
    INNER JOIN Comments ON Tags.comment_id = Comments.ID
    INNER JOIN Accounts ON Comments.author = Accounts.name
    CROSS JOIN (SELECT hive_per_vest FROM DynamicGlobalProperties) DynamicGlobalProperties


 
 
where Comments.depth = 0 and (comments.created AT TIME ZONE 'UTC') >= '2023-03-20 00:00:00' and (comments.created AT TIME ZONE 'UTC') <= '2023-03-21 06:00:00'


and Tags.tag = 'hivepowerbday'


order by 

comments.created DESC

Ahora filtrar por reputación y HP, quedando de la siguiente manera:

SELECT 
    Comments.author, 
    Comments.permlink,
    Accounts.reputation_ui AS REP, 
    (Accounts.vesting_shares * DynamicGlobalProperties.hive_per_vest) AS HP



FROM
    Tags
    INNER JOIN Comments ON Tags.comment_id = Comments.ID
    INNER JOIN Accounts ON Comments.author = Accounts.name
    CROSS JOIN (SELECT hive_per_vest FROM DynamicGlobalProperties) DynamicGlobalProperties


 
 
where 
    Comments.depth = 0 
    and (comments.created AT TIME ZONE 'UTC') >= '2023-03-20 00:00:00' 
    and (comments.created AT TIME ZONE 'UTC') <= '2023-03-21 06:00:00' 
    and Accounts.reputation_ui >= '39'
    and (Accounts.vesting_shares * DynamicGlobalProperties.hive_per_vest) < '25000'
    and Tags.tag = 'hivepowerbday'


order by 

comments.created DESC

Ahora viene la parte un poco más complicada y por crear el QUERY que filtre quien hizo Power UP mayor a 10 HIVE, así que lo hice de la siguiente manera:

SELECT 
    Comments.author, 
    Comments.permlink,
    Accounts.reputation_ui AS REP, 
    (Accounts.vesting_shares * DynamicGlobalProperties.hive_per_vest) AS HP,
    COALESCE(SUM(TxTransfers.amount), 0) AS POWER_UP
    
    



FROM
    Tags
    INNER JOIN Comments ON Tags.comment_id = Comments.ID
    INNER JOIN Accounts ON Comments.author = Accounts.name
    CROSS JOIN (SELECT hive_per_vest FROM DynamicGlobalProperties) DynamicGlobalProperties
    LEFT JOIN TxTransfers ON Comments.author = TxTransfers."from" AND TxTransfers.type = 'transfer_to_vesting'
        AND (TxTransfers.timestamp AT TIME ZONE 'UTC') >= '2023-03-20 00:00:00'
        AND (TxTransfers.timestamp AT TIME ZONE 'UTC') <= '2023-03-21 06:00:00'


 
 
where 
    Comments.depth = 0 
    and (comments.created AT TIME ZONE 'UTC') >= '2023-03-20 00:00:00' 
    and (comments.created AT TIME ZONE 'UTC') <= '2023-03-21 06:00:00' 
    and Accounts.reputation_ui >= '39'
    and (Accounts.vesting_shares * DynamicGlobalProperties.hive_per_vest) < '25000'
    and Tags.tag = 'hivepowerbday'
    and TxTransfers.amount >= '10'


group by 

    Comments.author,
    Comments.permlink,
    Comments.created,
    Accounts.reputation_ui,
    Accounts.vesting_shares,
    DynamicGlobalProperties.hive_per_vest
    


order by Comments.created DESC;

Parte del resultado:

Ya tengo todo lo basé para poder hacer una tabla para mostrar todos los usuarios que participaron en la iniciativa, pero ahora la pregunta es ¿Cómo hago eso?

Ok lo haré usando Python

Procedí a realizar la operación y quedó de la siguiente manera:

import pymssql
import configparser

# Read the configuration file
config = configparser.ConfigParser()
config.read('config.ini')

# Get the Hivesql credentials
hivesql_account = config.get('hivesql', 'account')
hivesql_password = config.get('hivesql', 'password')

connection = pymssql.connect(server='vip.hivesql.io',
                             database='DBHive',
                             user=hivesql_account,
                             password=hivesql_password)
cursor = connection.cursor()

SQLCommand = ("""
SELECT 
    Comments.author, 
    Comments.permlink,
    Accounts.reputation_ui AS REP, 
    (Accounts.vesting_shares * DynamicGlobalProperties.hive_per_vest) AS HP,
    COALESCE(SUM(TxTransfers.amount), 0) AS POWER_UP
FROM
    Tags
    INNER JOIN Comments ON Tags.comment_id = Comments.ID
    INNER JOIN Accounts ON Comments.author = Accounts.name
    CROSS JOIN (SELECT hive_per_vest FROM DynamicGlobalProperties) DynamicGlobalProperties
    LEFT JOIN TxTransfers ON Comments.author = TxTransfers."from" AND TxTransfers.type = 'transfer_to_vesting'
        AND (TxTransfers.timestamp AT TIME ZONE 'UTC') >= '2023-03-20 00:00:00'
        AND (TxTransfers.timestamp AT TIME ZONE 'UTC') <= '2023-03-21 06:00:00'
WHERE 
    Comments.depth = 0 
    AND (Comments.created AT TIME ZONE 'UTC') >= '2023-03-20 00:00:00' 
    AND (Comments.created AT TIME ZONE 'UTC') <= '2023-03-21 06:00:00' 
    AND Accounts.reputation_ui >= '39'
    AND (Accounts.vesting_shares * DynamicGlobalProperties.hive_per_vest) < '25000'
    AND Tags.tag = 'hivepowerbday'
    AND TxTransfers.amount >= '10'
GROUP BY
    Comments.author,
    Comments.permlink,
    Comments.created,
    Accounts.reputation_ui,
    Accounts.vesting_shares,
    DynamicGlobalProperties.hive_per_vest
ORDER BY
    Comments.created DESC;
""")



cursor.execute(SQLCommand)
results = cursor.fetchall()

data = []

data.append(["Usuario", "Link", "Reputación", "HP", "Power UP"])

for row in results:
    author, permlink, rep, hp, power_up = row
    author_link = f"[{author}](peakd.com/@{author})"
    post_link = f"[Link](peakd.com/@{author}/{permlink})"
    data.append([author_link, post_link, round(rep), round(hp), round(power_up)])

connection.close()

# Markdown 
with open('tabla.txt', 'w', encoding='utf-8') as file:
    # Write the header
    file.write("| " + " | ".join(data[0]) + " |\n")
    file.write("|" + "----|" * len(data[0]) + "\n")

    # Write the data
    for row in data[1:]:
        file.write("| " + " | ".join([str(col) for col in row]) + " |\n")

print("Datos guardados en tabla.txt")



------------

Use la librería de pymssql para conectarme a HIVESQL, también usé un config.ini para guardar mis credenciales de HiveSQL

Luego que hice unas pruebas y adapté todo a lo que necesitaba que es una tabla que me permita pegar en peakd, el resultado es el siguiente.

Pero hagamos que sea más divertida la tabla, ordenando por la cantidad de HP colocado en Stake y que pueda en enumerar las filas.

Espero que le funcione a @victoriabsb toda la información suministrada en la publicación.

El que es programador puede copiar mi código y usarlo.