[GitHub] [superset] nicovak commented on issue #18832: Pivot Table V2 - Wrong data for month interval

[GitBox <gi...@apache.org>]

nicovak commented on issue #18832:
URL: https://github.com/apache/superset/issues/18832#issuecomment-1055130774


   You're welcome @villebro 
   Here are the answers, sorry for the delay.
   
   - Latest docker tag (just updated now)
   - MongoDB with Trino
   
   I'm pretty sure the issue comes from the dashboard itself, maybe the way month's range is built. It looks like It takes 1 year, but the result is not the same (see my previous screenshots). 
   
   ## Table V1
   
   ```sql
   SELECT company AS company,
          date_trunc('month', CAST(created_at AS TIMESTAMP)) AS created_at,
          count(DISTINCT contact_id) AS "COUNT_DISTINCT(contact_id)"
   FROM
     (SELECT **************.company,
             contact_id,
            **************,
             **************.created_at
      FROM **************
      INNER JOIN ************** ON ************** = **************
      INNER JOIN ************** ON ************** = **************
      WHERE ************** IN('**************',
                          '**************',
                          '**************',
                          '**************')
        AND ************** = true
        AND ************** = false) AS virtual_table
   GROUP BY company,
            date_trunc('month', CAST(created_at AS TIMESTAMP))
   LIMIT 50000;
   ```
   
   ## Table V2
   
   ```sql
   SELECT date_trunc('month', CAST(created_at AS TIMESTAMP)) AS created_at,
          company AS company,
          count(DISTINCT contact_id) AS "COUNT_DISTINCT(contact_id)"
   FROM
     (SELECT **************.company,
             contact_id,
             **************,
             **************.created_at
      FROM **************
      INNER JOIN ************** ON ************** = **************
      INNER JOIN************** ON ************** =**************
      WHERE ************** IN('**************',
                          '**************',
                          '**************',
                          '**************')
        AND ************** = true
        AND ************** = false) AS virtual_table
   GROUP BY date_trunc('month', CAST(created_at AS TIMESTAMP)),
            company
   ORDER BY "COUNT_DISTINCT(contact_id)" DESC
   LIMIT 50000;
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org