SQL Query to Power BI

[NataliaMC2]

Hello,

I need to know  the number of new users registered by branch. To do it, i need to know the date of the first transaction by user (uid), and compare it with the registered date that the trade reported, to finally count it. This is the query i have to do it.

SELECT
DATE_FORMAT(B.fecha_registro,'%Y-%m') mes,
B.id_sucursal,
B.id_comercio,
COUNT(*)
FROM
(SELECT
A.uid,
A.id_sucursal,
A.id_comercio,
A.fecha_registro,
TIMESTAMPDIFF(DAY, A.fecha_registro ,A.fecha) diffRegistroTx
FROM
(SELECT
t1.uid,
t1.fecha_registro,
t2.fecha,
t2.id_sucursal,
t2.id_comercio,
ROW_NUMBER() OVER (PARTITION BY t2.id_comercio, t2.uid ORDER BY t2.fecha ASC) top
FROM
xxx.usuarios_comercios t1
LEFT JOIN
xxx.historial_puntos t2 ON t1.uid = t2.uid AND t1.id_comercio=t2.id_comercio
WHERE
t1.id_comercio = 12
AND t2.tipo IN('carga','redencion','promocion')
AND t1.uid NOT IN(SELECT tt2.uid FROM xxx.usuarios tt2 WHERE tt2.estado='inactivo')) A
WHERE
A.top = 1) B
GROUP BY
mes,
B.id_sucursal;

Thank you for help me!

[dax]

Hi NataliaMC2, 

If possible, could you please inform me more detailed information(such as your sample data and your expected output)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NataliaMC2]

Hi Zoe, 

Thank you for reply.

This is the case. I have three tables:

1. Points: Contains the transactions

2. Users: Contains the information of users

2. users_brands: Contains the number of brands where an user is registered

In need to know how many new users we have by branch, to know it i assume that i have a new user when they do their first transaction, so i need to know their first transaction by branch and compare it with the date in which the brand reported the registration of this user. On the other hand, some people can be registered by app, so they didn't do any transaction those people have another category "not assigned". At the end what i want to draw is it:

To do it, I developed 2 SQL Query, that I want to transform it to Power BI. The first one, calculate the number of users by brand, comparing the date of their first transaction vs the date in which the brand reported it:

SELECT
DATE_FORMAT(B.fecha_registro,'%Y-%m') mes,
B.id_branch,
COUNT(*)
FROM
(SELECT
A.uid,
A.id_branch,
A.fecha_registro,
TIMESTAMPDIFF(DAY, A.fecha_registro ,A.fecha) diffRegistroTx
FROM
(SELECT
t1.uid,
t1.fecha_registro,
t2.fecha,
t2.id_branch,
ROW_NUMBER() OVER (PARTITION BY t2.id_brand, t2.uid ORDER BY t2.fecha ASC) top
FROM
loyal.users_brands t1
LEFT JOIN
loyal.points t2 ON t1.uid = t2.uid AND t1.id_comercio=t2.id_comercio
WHERE
AND t2.tipo IN('carga','redencion','promocion')
AND t1.uid NOT IN(SELECT tt2.uid FROM loyal.users tt2 WHERE tt2.estado='inactivo')) A
WHERE
A.top = 1) B
GROUP BY
mes,
B.id_sucursal;

And the second one, calculate the users who are registered but the didn't have any transaction, "Not Assigned":
SELECT
DATE_FORMAT(t1.fecha_registro, '%Y-%m') mes,
COUNT(*) registros
FROM
loyal.users_brands t1
WHERE
t1.id_comercio=12
AND t1.uid NOT IN(SELECT tt1.uid FROM loyal.points tt1 WHERE tt1.tipo IN('carga','redencion','promocion') AND tt1.id_comercio=12 GROUP BY tt1.uid)
AND t1.uid NOT IN(SELECT tt2.uid FROM loyal.users tt2 WHERE tt2.estado='inactivo')
GROUP BY
mes;

Thank you for help me!!!