Count No of Records Based on unique ID

Trying to count number of unique records for each customer id in beast mode. Currently using:

COUNT(DISTINCT 

   CASE 

    WHEN `unique ID`=`unique ID`

    THEN COUNT(DISTINCT `Records`)

   END)


Any help? Keep getting error message.

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    You can't do a count distinct inside of another count distinct. Also, unique id will always equal unique id because it evaluates by row and so you are comparing the value to itself.

    Have you tried just COUNT(DISTINCT 'Records')) That actually seems to be what you are after if you have the customer id as one column and then the count of distinct records as another column.

  • Giacomo
    Giacomo ⚪️

    I think you're looking to use a Windowing function within a Beast Mode, however I do not believe that you can do a count distinct within a Windowing function ( I could be wrong on that). If it's an option you may want to consider doing it within a Magic ETL using the Rank & Window tile.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    edited April 29

    Both @MarkSnodgrass and @Giacomo are correct, you cannot nest Count Unique inside a Window Function.

    ... well.... you can, but the answer will be 1.

    What's your use case? What question are you trying to answer?