DBFiddle Link: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=2d7e9a4ddfdc8fb619a8dfc76d767950

Hi. I have one table called 'Model Versions' which has the fields and records as below.

Expected Output:

I wanted to get the top 3 versions of each client along with their respective sentence count. My expected output looks like below:

I tried writing a query with multiple CTEs and Partition By's. But none worked out. Seeking your help to achieve this.

DBFiddle Link: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=2d7e9a4ddfdc8fb619a8dfc76d767950

CodePudding user response：

You did not specify which of the top 3 version you wish to fetch. I'll assume you want to retrieve the 3 latest versions, based on creation date.

My suggestion is to use a ROW_NUMBER() for each client in a windowed function, and to filter the top 3 rows.

For instance :

with cte as(
  select 
   client,
   version,
   version_created_at,
   count(Sentence) total_count_of_sentences_per_version,
   row_number() over(partition by client order by version_created_at desc) version_row_number
 from model_versions
 group by
   client,version,
   version_created_at
 )
select
  client,
  version,
  total_count_of_sentences_per_version,
  version_created_at
from cte
where version_row_number <=3

Try it online

CodePudding user response：

You can try this:

WITH main_tab
     AS (SELECT client,
                version,
                Count(*)
                  OVER (
                    partition BY client, version),
                Min(version_created_at)
                  OVER (
                    partition BY client, version),
                Dense_rank()
                  OVER (
                    partition BY client
                    ORDER BY version DESC) rn
         FROM   model_versions)
SELECT DISTINCT m.*
FROM   main_tab m;