Window function firstValue, secondValue, Nth_Value, lastValue

I have offer statistics for list of offer received from multiple partner with CPC value. Here, I am interested to shows the Summary table as follows.

Request Id - It is common offer id shared by each perks.
Offer Count - Total offer received per perk
Publisher - it’s name of publisher from which we received this offer.
Max CPC - It’s maximum CPC among all the received offers.
Min CPC - it’s minimum CPC among all the received offers.
Avg CPC - it’s average CPC among all the received offers.
Offer 1 Partner - Highest CPC partner (Ideally Max CPC Partner Name)
Offer 1 CPC - Highest CPC value
Offer 2 Partner - Second highest CPC Partner
Offer 2 CPC - Second highest CPC value
Offer 3 Partner - Third highest CPC Partner
Offer 3 CPC - Third highest CPC value

Here in database table data store as following columns
id, publisher_name, partner_name, cpc, request Id

I can get the information about Offer 1 Partner using firstValue function.

How can I get the second and third highest cpc and their partner name ?

1 Like

Hello @margil, welcome to the QuickSight community!

Based on your explanation, I believe the best way to retrieve the top 3 CPC values would be to utilize the denseRank calculation.

This would allow you to rank the values, then to return the CPC value and partner name you could use an ifelse statement. It would look something like this:

ifelse({rank} = 2, {CPC}, NULL)
ifelse({rank} = 2, {Partner}, NULL)

Then you could get the top 3 and even the worst ranking Partners by CPC. Let me know if this helps!

Hi @DylanM,

Thanks for your reply!

I have already used suggested solution before posting here! It cause a problem in displaying the data as below sample.

|Requested ID | Offer Count | Publisher | Max CPC | Min CPC | Avg CPC | Offer 1 Partner | Offer 1 CPC | Offer 2 Partner | Offer 2 CPC | Offer 3 Partner | Offer 3 CPC|
|abc123 | 15 | XYZ | 10 | 2 | 5 | ABC | 10 | NULL | NULL | NULL | NULL|
|abc123 | 15 | XYZ | 10 | 2 | 5 | NULL | NULL | DEF | 8 | NULL | NULL|
|abc123 | 15 | XYZ | 10 | 2 | 5 | NULL | NULL | NULL | NULL | WXZ | 7|
|abc123 | 15 | XYZ | 10 | 2 | 5 | NULL | NULL | NULL | NULL | NULL | NULL|

Above table shows the duplicating of records instead of single Request Id.

So, I think I can not use this solution.

1 Like

Hello @margil, how are you building out the denseRank function? Maybe we can alter that calculation to figure this out. Also, what would be the expected output? Could a single request ID be linked to more than 1 of the top 3 values?

The best way for me to assist here would be if you could create a demo version of this analysis in QuickSight Arena, publish the dashboard, and share the link over with me. That would allow me to test some possible solutions and send them over to you in QuickSight to view.

Hi @DylanM
I have build the denseRank on the basis of CPC value in descending order and partition by tracking_id, publisher_name.

You may refer the publisher Arena:
Offer Dashboard

I am looking for the same output! But wanted to have single row per Requested ID

Hello @margil, I went ahead and created a new version of the analysis you shared over.

Window function firstValue, secondValue, Nth_Value, lastValue

I can explain the limitation. Basically, by including the Offer 1, 2, or 3 Partner names in the table as well as the Offer Rank, you are going to create multiple rows for each tracking_id field. If we are referencing a value like Offer 1 CPC, we can utilize minOver partitioned by tracking_id to ensure it will appear on the same row. Once multiple partitions are added for the tracking_id, it will populate multiple rows. The Partner name will need to be excluded if you do not want multiple rows because I can’t use minOver to bring it in on a single row.

Let me know if this helps!

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

Hi @DylanM,
It is good to see your reply.

I can understand that scenario very well, Offer Rank is just for understanding purpose. I don’t want that in table. Interested to have single row per tracking_id their count publisher name and Offer 1 to 3 Partner name along with their CPC value.

I can fetch the data using following SQL but that creates load on database that’s why I am interested to render the data using QuickSight.

SELECT tracking_id, publisher_name,
first_value(cpc) OVER (PARTITION BY tracking_id, publisher_name ORDER BY cpc DESC) AS offer_1_cpc,
first_value(partner_name) OVER (PARTITION BY tracking_id, publisher_name ORDER BY cpc DESC) AS offer_1_partner

FROM table

So, Is there any solution for above issue in QuickSight ?

Hello @margil, what database are you using as your datasource connection in QuickSight? Setting up a seperate dataset that would have some of these aggregations within the SQL shouldn’t be a huge constraint on your database. How many rows of data are you ingesting?

Alternatively, there is the firstValue calculation that you can use in QuickSight:

From what I can tell, this should function the same way as the SQL statement you wrote above.