66 lines
1.7 KiB
SQL
66 lines
1.7 KiB
SQL
WITH `ranking_list` AS (
|
|
SELECT
|
|
`n`.`id` AS `id`,
|
|
`n`.`nickname` AS `nickname`,
|
|
`n`.`address` AS `address`,
|
|
`n`.`stakedBalance` AS `stakedBalance`,
|
|
`n`.`computingPower` AS `computingPower`,
|
|
`n`.`childrenAccumulatedBalance` AS `childrenAccumulatedBalance`
|
|
FROM
|
|
`DKON`.`Node` `n`
|
|
ORDER BY
|
|
`n`.`stakedBalance` DESC
|
|
)
|
|
SELECT
|
|
`ranking_list`.`id` AS `id`,
|
|
`ranking_list`.`nickname` AS `nickname`,
|
|
`ranking_list`.`address` AS `address`,
|
|
round((`ranking_list`.`stakedBalance` / pow(10, 18)), 4) AS `stakedBalance`,
|
|
round((`ranking_list`.`computingPower` / pow(10, 18)), 4) AS `computingPower`,
|
|
round(
|
|
(
|
|
`ranking_list`.`childrenAccumulatedBalance` / pow(10, 18)
|
|
),
|
|
4
|
|
) AS `childrenAccumulatedBalance`,
|
|
`ranking_list`.`stakedBalance` AS `stakedBalanceOrig`,
|
|
`ranking_list`.`computingPower` AS `computingPowerOrig`,
|
|
`ranking_list`.`childrenAccumulatedBalance` AS `childrenAccumulatedBalanceOrig`,
|
|
(
|
|
`ranking_list`.`stakedBalance` / (
|
|
SELECT
|
|
sum(`ranking_list`.`stakedBalance`)
|
|
FROM
|
|
`ranking_list`
|
|
)
|
|
) AS `staked_portion`,
|
|
(
|
|
`ranking_list`.`computingPower` / (
|
|
SELECT
|
|
sum(`ranking_list`.`computingPower`)
|
|
FROM
|
|
`ranking_list`
|
|
)
|
|
) AS `computingPower_portion`,
|
|
(
|
|
`ranking_list`.`childrenAccumulatedBalance` / (
|
|
SELECT
|
|
sum(`ranking_list`.`childrenAccumulatedBalance`)
|
|
FROM
|
|
`ranking_list`
|
|
)
|
|
) AS `childrenAccumulatedBalance_portion`,
|
|
(
|
|
(
|
|
`ranking_list`.`computingPower` / (
|
|
SELECT
|
|
sum(`ranking_list`.`computingPower`)
|
|
FROM
|
|
`ranking_list`
|
|
)
|
|
) * 66666.666666666666
|
|
) AS `computingPower_estimated_dist`
|
|
FROM
|
|
`ranking_list`
|
|
GROUP BY
|
|
`ranking_list`.`address` |