Help with visualization: view only top 10

[arindamchoudhur]

Hi,

I want to create a visualization where i want to show top 10 playerName with most sum of counter by dayDate.

 

I reached till here. But i don't know how to limit top 10 playerName.

Any help will be highly appreciated.

[szymon_dybczak]

Hi @arindamchoudhur ,

Try the approach suggested at below answer:

Re: How to Display Top Categories in Databricks AI... - Databricks Community - 93888

[szymon_dybczak]

Oh, maybe there is an easier way to achieve that. According to release notes they've added ability to choose Top or bottom N categories in bar chart. To edit, open the kebab menu for the categorical axis dimension and set the number under Default number of categories:

[arindamchoudhur]

@szymon_dybczak 

Default Number of Categories controls the number of dayDate, not the PlayerName.

[szymon_dybczak]

Sorry, didn't notice that. But still, second approach should work. Here I have parametrized my dataset:

Then on my bar chart visualization I applied that limit_number parameter. As you can see when I set limit_number = 5 then I 've got top 5 countries with the highest revenue at given year-month:

When I changed my parameter number to 3 I've got different result (top 3 countries this time):

[arindamchoudhur · Accepted solution]

Finally what i did is:

create a new data source with is query:

WITH daily_totals AS (
SELECT
event_date,
playerName,
SUM(counter) AS total_counter
FROM your_table
GROUP BY event_date, playerName
),
ranked AS (
SELECT
event_date,
playerName,
total_counter,
ROW_NUMBER() OVER (
PARTITION BY event_date
ORDER BY total_counter DESC
) AS rn
FROM daily_totals
)
SELECT
event_date,
playerName,
total_counter
FROM ranked
WHERE rn <= 10
ORDER BY event_date, total_counter DESC;