Add first lead date to my product without breaking product rows

[savordelli]

I have the following CTE running an explore (redshift). The idea behind it is to check product performance and it was going well and I had a line for each product, however, when tried to put the date of the first lead received into the table i stumbled upon a problem.

I only found a way of doing that through Table Calculations, but to do that i have to include the lead date into the table and that breaks my products into multiple lines. Like that:

Is there another way to do this?

```
WITH products AS (
SELECT
product_table.*,
some custom columns,
some leads columns,

ROW_NUMBER() OVER (PARTITION BY product.product_id ORDER BY lead.createdat DESC) AS row_num
FROM lead table lead
INNER JOIN tables on product_id
GROUP BY each column used
)
SELECT * FROM products
```

[Shru]

Trying to understand here, so the CTE used to bring one line per product. But if you introduce the date column it opens up to multiple lines. ( i hope this is the issue)

Are there timestamps with the date value, which is causing it to open up? else the row_num=1 should have taken care of this. You can try casting it to only date value and check.

Another option would be to create a view with another derived query which only gives the product_id and its 1st lead date as the output. Then join this  view with your original view based on product_id in the explore. You should then be able to use the 1st lead date in your report without breaking data into multiple lines.

[savordelli]

@Shru that is indeed what is happening, however i cannot use row_num = 1 because that would limit the results for leads of different clients that came after the first lead (and because i ordered it DESC for me some reason)