Explorando HP/Actividad/HiveSQL (Análisis de Datos) ENG/SPA

freepik__the-style-is-candid-image-photography-with-natural__19079.png

Hola Hivers, espero lo mejor para todos ustedes y ahora les traigo un post diferente. Sé que programar es importante pero conocer algunos datos interesantes también es una buena oportunidad. Piense también en la mayoría y tener diferentes puntos de vista nos dará "herramientas" para profundizar en las métricas sociales de Hive.

ENGLISH BELOW

Hive se distingue como una blockchain social y descentralizada, un ecosistema donde la interacción va más allá de simplemente compartir contenido. Aquí, cada usuario tiene el potencial de influir y ser recompensado, y el motor central de esta participación es el Poder de Voto (HP - Hive Power). Acumular HP es esencial porque otorga peso a tu voz y tus acciones dentro de la red.

El HP es crucial para la curación, permitiéndote impulsar la visibilidad de contenido de calidad y obtener recompensas por ello.
Un mayor HP amplifica el valor de tu voto, impactando directamente en la distribución de las recompensas.
Además, el HP es la base de la gobernanza de Hive, otorgando derecho a votar por los "Witnesses" (los productores de bloques) y las propuestas que definen el rumbo tecnológico y económico de la blockchain.

Tu HP, por tanto, no es solo una medida de valor, sino una representación tangible de tu participación e influencia en este entorno digital único.

YES JACK YES

Aprovecho para llamar al PowerUP

Siempre que puedas por favor: haz powerUp. Es tan simple como:

UNO: convierte cripto/dinero fisico o virtual a HIVE.
DOS:
- Hive Keychain: clic en HIVE, clic en POWER UP, clic en max
- Confirma y listo! Gracias

Descubriste el agua tibia wow(ironico)

Si. Descubri HiveSQL y no puedo dejar de hacer consultas por su rapidez, por su versatilidad, por su belleza y su complejidad para hacer consultas tan complejas como la del grafico:

WITH RecentActiveUsers AS (
    -- Esta CTE identifica a todos los autores distintos que han publicado UN POST O UN COMENTARIO
    -- en los ÚLTIMOS 2 MESES.
    SELECT DISTINCT author
    FROM Comments -- La tabla Comments incluye tanto posts (parent_author='') como comentarios (parent_author<>'')
    WHERE created >= DATEADD(month, -2, GETDATE()) -- **Actividad en los últimos 2 meses**
)
SELECT
    -- Contar USUARIOS ACTIVOS (en los últimos 2 meses) con VESTS netos <= ~300 HP
    COUNT(CASE WHEN (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) <= 700000 THEN a.name ELSE NULL END) AS Count_Active_2M_Low_HP_Approx,

    -- Contar USUARIOS ACTIVOS (en los últimos 2 meses) con VESTS netos > ~300 HP
    COUNT(CASE WHEN (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) > 700000 THEN a.name ELSE NULL END) AS Count_Active_2M_Higher_HP_Approx

FROM
    Accounts a -- Desde la tabla de cuentas
JOIN
    RecentActiveUsers rau ON a.name = rau.author -- Unir SOLO con los usuarios identificados como activos (en los últimos 2 meses)
;

Que podamos en aproximadamente Total execution time: 00:00:02.794 conocer los usuarios activos que desde hace 2 meses han publicado al menos 1 comentario o 1 post y que entren en una de dos categorias: menos de 300HP y el resto con mas de eso...en realidad muuucho mas.

Como lo hago si quiero programar un HIVESQL playground?

Crea tu cuenta siguiendo los pasos oficiales
Descarga Azure Data Studio
Activa la conexion:
- En la pantalla de bienvenida, busca "Nueva Conexion"
- Rellena los campos y fijate bien en mi proxima imagen:

Al conectarse veras un punto verde en la seccion conexiones.
Clic derecho sobre la conexion activa y selecciona "Nueva Consulta"
Rellenas la consulta como lo desees y voilá clic en Ejecutar o Run.

Algunas consultas interesantes por aca debajo:

WITH RecentActiveUsers AS (
    -- Esta CTE identifica a todos los autores distintos que han publicado un post en la última semana
    SELECT DISTINCT author
    FROM Comments
    WHERE parent_author = '' -- Solo posts principales
      AND created >= DATEADD(week, -1, GETDATE()) -- Posts en la última semana
)
SELECT
    -- Contamos los usuarios de la CTE (activos) y los clasificamos por su poder de voto (VESTS)
    COUNT(DISTINCT CASE WHEN (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) <= 700000 THEN a.name ELSE NULL END) AS Count_le_300_HP,
    COUNT(DISTINCT CASE WHEN (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) > 700000 AND (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) <= 1400000 THEN a.name ELSE NULL END) AS Count_301_to_600_HP,
    COUNT(DISTINCT CASE WHEN (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) > 1400000 AND (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) <= 2100000 THEN a.name ELSE NULL END) AS Count_601_to_900_HP,
    COUNT(DISTINCT CASE WHEN (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) > 2100000 AND (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) <= 2800000 THEN a.name ELSE NULL END) AS Count_901_to_1200_HP,
    COUNT(DISTINCT CASE WHEN (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) > 2800000 THEN a.name ELSE NULL END) AS Count_gt_1200_HP
FROM
    Accounts a -- Seleccionamos de la tabla de cuentas
JOIN
    RecentActiveUsers rau ON a.name = rau.author -- Unimos solo con los usuarios identificados como activos en la CTE
;

Esta de aca arriba: "Usuarios que considero activos al haber publicado al menos 1 articulo y los separamos en varios grupos segun su HP" y graficamente usando Google Sheets:

Entonces ademas de llamar a usar todo esto a que mas llamas?

Pues llamo a los usuarios para darnos cuenta que la clave esta en impulsar a la masa, a la mayoria que tiene menos de 300HP. Impulsar a los usuarios y desarrollar herramientas para mejorar su poder y no solo eso sino:

Su calidad de vida
Su nivel educativo
Sus maneras de promocionar HIVE
Sus recompensas en un 400%

Ellos son la clave y deberian ser el enfoque del trabajo de la minoria como nosotros que pasamos de 1000HP

Crear herramientas necesarias para que ellos multiplequen todo: monedas, contenido e impulso!

No! No se trata de una vision burda de "socialismo" no me confundas ni por un segundo. Yo vivo en Venezuela y he vivido en mas de 10 paises asi que se de primera mano que el socialismo es un ideal corrupto y sin sentido. Lo que para mi tiene sentido es trabajar en pro de las personas, no del dinero solamente! Y Hacer cosas creativas para consolidar un ideal por el que muchos han muerto ya "Ama a otros como a ti mismo" pues la vida es solo un segundo, en relacion al tiempo de la tierra.

Los Gigantes de Hive: El Top 1000 de HP

Consulta HiveSQL como:

WITH LatestGlobalProps AS (
    -- ADVERTENCIA: Sin ORDER BY, TOP 1 obtendrá un registro ARBITRARIO,
    -- no necesariamente el más reciente. Esto afectará la precisión de EstimatedHivePower y EstimatedHivePowerUSD.
    SELECT TOP 1
        total_vesting_shares,
        total_vesting_fund_hive
        -- HiveSQL también tiene una columna price_hive_usd en esta tabla, pero para usar tu valor específico (0.234),
        -- simplemente lo multiplicamos al final. Si quisieras usar el precio de la DB, podrías seleccionarlo aquí también.
    FROM DynamicGlobalProperties
    -- Se omite ORDER BY como solicitaste, pero NO se recomienda para obtener datos recientes
)
SELECT TOP 1000 -- Mostramos los 1000 usuarios con más HP (VESTS netos)
    a.name, -- Nombre del usuario
    (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) AS NetVestingShares, -- VESTS netos (medida exacta del poder de voto)

    -- Calcular el equivalente aproximado en HP (en unidades HIVE) - Usando la tasa de la blockchain
    CAST(
        (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares)
        * (CAST(lgp.total_vesting_fund_hive AS DECIMAL(38, 10)) / CAST(lgp.total_vesting_shares AS DECIMAL(38, 10))) -- Tasa de conversión VESTS a HIVE
        AS DECIMAL(18, 3) -- Convertir a decimal con 3 decimales para mostrar HP
    ) AS EstimatedHivePower, -- HP estimado en HIVE

    -- Calcular el valor aproximado en USD
    CAST(
        (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares)
        * (CAST(lgp.total_vesting_fund_hive AS DECIMAL(38, 10)) / CAST(lgp.total_vesting_shares AS DECIMAL(38, 10))) -- VESTS a HIVE tasa
        * 0.234 -- Tasa HIVE a USD proporcionada por el usuario (0.234)
        AS DECIMAL(18, 2) -- Convertir a decimal con 2 decimales para USD (formato moneda)
    ) AS EstimatedHivePowerUSD -- HP estimado en USD

FROM
    Accounts a -- Desde la tabla de cuentas
CROSS JOIN
    LatestGlobalProps lgp -- Unir con las propiedades globales obtenidas (arbitrariamente sin ORDER BY)
ORDER BY
    NetVestingShares DESC; -- Opcionalmente, podrías ordenar por EstimatedHivePowerUSD DESC

Esto arroja unos grandes numeros de poder, valor y peso solo para el top 1000 de HIVE. No se impresionen con la siguiente foto por favor:

Cierro pidiendo tu opinion sobre:

¿Es bueno o malo para la descentralización?
¿Cómo afecta a los nuevos usuarios con poco poder?
¿Cuál es el papel de los usuarios de bajo HP si son la mayoría en número pero minoría en poder de voto?

Asi que te impulso a comentar. Al mejor comentario le dare un regalo especial cuando la publicacion haya sido cobrada!

ENGLISH Below

Hello Hivers, I hope the best for all of you and now I bring you a different post. I know that programming is important but knowing some interesting data is also a good opportunity. Also think about the majority and having different points of view will give us "tools" to delve deeper into Hive's social metrics.

ENGLISH BELOW

Hive distinguishes itself as a social and decentralized blockchain, an ecosystem where interaction goes beyond simply sharing content. Here, each user has the potential to influence and be rewarded, and the central driver of this participation is Voting Power (HP - Hive Power). Accumulating HP is essential because it gives weight to your voice and your actions within the network.

HP is crucial for curation, allowing you to boost the visibility of quality content and earn rewards for it.
Higher HP amplifies the value of your vote, directly impacting the distribution of rewards.
In addition, HP is the basis of Hive's governance, granting the right to vote for "Witnesses" (block producers) and the proposals that define the technological and economic direction of the blockchain.

Your HP, therefore, is not only a measure of value, but a tangible representation of your participation and influence in this unique digital environment.

YES JACK YES

I take this opportunity to call PowerUP

Whenever you can please: do powerUp. It's as simple as:

ONE: convert crypto/physical or virtual money to HIVE.
TWO:
Hive Keychain: click HIVE, click POWER UP, click max
Confirm and that's it! Thank you

You discovered warm water wow(ironic)

Yeah. I discovered HiveSQL and I can't stop making queries because of its speed, its versatility, its beauty and its complexity to make queries as complex as the graph:

WITH RecentActiveUsers AS (
    -- This CTE identifies all the different authors who have published A POST OR A COMMENT
    -- in the LAST 2 MONTHS.
    SELECT DISTINCT author
    FROM Comments -- The Comments table includes both posts (parent_author='') and comments (parent_author<>'')
    WHERE created >= DATEADD(month, -2, GETDATE()) -- **Activity in the last 2 months**
)
SELECT
    -- Count ACTIVE USERS (in the last 2 months) with net VESTS <= ~300 HP
    COUNT(CASE WHEN (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) <= 700000 THEN a.name ELSE NULL END) AS Count_Active_2M_Low_HP_Approx,

    -- Count ACTIVE USERS (in the last 2 months) with net VESTS > ~300 HP
    COUNT(CASE WHEN (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) > 700000 THEN a.name ELSE NULL END) AS Count_Active_2M_Higher_HP_Approx

FROM
    Accounts a -- From the chart of accounts
JOIN
    RecentActiveUsers rau ON a.name = rau.author -- Join ONLY with users identified as active (in the last 2 months)
;

That we can in approximately Total execution time: 00:00:02.794 know the active users who for 2 months have published at least 1 comment or 1 post and who fall into one of two categories: less than 300HP and the rest with more of that...actually much more.

NOTE: as I am using a translator for the ENG content some queries may be affected when translated I can only guarantee about the above in spanish parts as tested and working properly

How do I do it if I want to program a HIVESQL playground?

Create your account by following the official steps
Download Azure Data Studio
Activate the connection:
On the welcome screen, search for "New Connection"
Fill in the fields and look closely at my next image:

When you connect you will see a green dot in the connections section.
Right click on the active connection and select "New Query"
Fill out the query as you wish and voila click Run or Run.

Some interesting questions below:

WITH RecentActiveUsers AS (
    -- This CTE identifies all the different authors who have published a post in the last week
    SELECT DISTINCT author
    FROM Comments
    WHERE parent_author = '' -- Main posts only
      AND created >= DATEADD(week, -1, GETDATE()) -- Posts in the last week
)
SELECT
    -- We count CTE users (active) and classify them by their voting power (VESTS)
    COUNT(DISTINCT CASE WHEN (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) <= 700000 THEN a.name ELSE NULL END) AS Count_le_300_HP,
    COUNT(DISTINCT CASE WHEN (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) > 700000 AND (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) <= 1400000 THEN a.name ELSE NULL END) AS Count_301_to_600_HP,
    COUNT(DISTINCT CASE WHEN (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) > 1400000 AND (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) <= 2100000 THEN a.name ELSE NULL END) AS Count_601_to_900_HP,
    COUNT(DISTINCT CASE WHEN (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) > 2100000 AND (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) <= 2800000 THEN a.name ELSE NULL END) AS Count_901_to_1200_HP,
    COUNT(DISTINCT CASE WHEN (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) > 2800000 THEN a.name ELSE NULL END) AS Count_gt_1200_HP
FROM
    Accounts a -- We select from the chart of accounts
JOIN
    RecentActiveUsers rau ON a.name = rau.author -- We join only with users identified as active in the CTE
;

It is from above: "Users that I consider active having published at least 1 article and we separate them into several groups according to their HP" and graphically using Google Sheets:

So besides calling to use all this, what else are you calling?

Well, I call the users to realize that the key is to boost the mass, the majority that has less than 300HP. Boost users and develop tools to improve their power and not only that but:

Your quality of life
Your educational level
Your ways to promote HIVE
Your rewards at 400%

They are the key and should be the focus of minority work like us who go over 1000HP

Create necessary tools for them to multiply everything: coins, content and momentum!

No! This is not a crude vision of "socialism", do not confuse me for a second. I live in Venezuela and I have lived in more than 10 countries so I know firsthand that socialism is a corrupt and meaningless ideal. What makes sense to me is working for people, not just money! And do creative things to consolidate an ideal for which many have already died "Love others as yourself" because life is only a second, in relation to earth time.

The Hive Giants: HP's Top 1000

See HiveSQL as:

WITH LatestGlobalProps AS (
    -- WARNING: Without ORDER BY, TOP 1 will obtain an ARBITRARY record,
    -- not necessarily the most recent. This will affect the accuracy of EstimatedHivePower and EstimatedHivePowerUSD.
    SELECT TOP 1
        total_vesting_shares,
        total_vesting_fund_hive
        -- HiveSQL also has a price_hive_usd column in this table, but to use your specific value (0.234),
        -- we simply multiply it at the end. If you would like to use the price of the DB, you could select it here as well.
    FROM DynamicGlobalProperties
    -- ORDER BY is omitted as you requested, but it is NOT recommended to obtain recent data
)
SELECT TOP 1000 -- We show the 1000 users with the most HP (net VESTS)
    a.name, -- User name
    (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares) AS NetVestingShares, -- NET VESTS (exact measure of voting power)

    -- Calculate the approximate equivalent in HP (in HIVE units) - Using the blockchain rate
    CAST(
        (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares)
        * (CAST(lgp.total_vesting_fund_hive AS DECIMAL(38, 10)) /CAST(lgp.total_vesting_shares AS DECIMAL(38, 10))) -- VESTS to HIVE conversion rate
        AS DECIMAL(18, 3) -- Convert to decimal with 3 decimal places to display HP
    ) AS EstimatedHivePower, -- HP estimated at HIVE

    -- Calculate the approximate value in USD
    CAST(
        (a.vesting_shares + a.received_vesting_shares - a.delegated_vesting_shares)
        * (CAST(lgp.total_vesting_fund_hive AS DECIMAL(38, 10)) /CAST(lgp.total_vesting_shares AS DECIMAL(38, 10))) -- VESTS at HIVE rate
        * 0.234 -- HIVE to USD rate provided by the user (0.234)
        AS DECIMAL(18, 2) -- Convert to decimal with 2 decimal places for USD (currency format)
    ) AS EstimatedHivePowerUSD -- HP estimated in USD

FROM
    Accounts a -- From the chart of accounts
CROSS JOIN
    LatestGlobalProps lgp -- Join with the global properties obtained (arbitrarily without ORDER BY)
ORDER BY
    NetVestingShares DESC; -- Optionally, you could order by EstimatedHivePowerUSD DESC

This throws up some big numbers of power, value and weight just for HIVE's top 1000. Please don't be impressed with the following photo:

I close by asking your opinion on:

Is it good or bad for decentralization?
How does it affect new users with little power?
What is the role of low HP users if they are the majority in number but minority in voting power?

So I encourage you to comment. For the best comment I will give you a special gift when the publication has been charged! (the comment with more votes of course)