I'm using the above code to perform a left join on jira.releases table:

SELECT b.*,c.releasedate from digitalpfatforms.issues_braze b
Left join jira.releases c
On b.version_id=c.versionid

With this I get the following intended output:

Out of this output I need to calculate 2 dense_rank(). One over sprintenddate:

DENSE_RANK() OVER(ORDER BY b.sprintenddate DESC) AS "rank_sprint"

and the other over releasedate:

DENSE_RANK() OVER(ORDER BY c.releasedate DESC) AS "rank_release"

My intended output is the:

Can anyone help me on how to put together the SQL query so that it perfoms a join and calculates the mentioned dense_rank?

Thank you

CodePudding user response：

Looks like you did all of the work already. Add the pieces together and it will work.

SELECT b.*,
c.releasedate,
DENSE_RANK() OVER(ORDER BY b.sprintenddate DESC) AS "rank_sprint",
DENSE_RANK() OVER(ORDER BY c.releasedate DESC) AS "rank_release"
from digitalpfatforms.issues_braze b
left join jira.releases c on b.version_id=c.versionid