Business Intelligence academic work over mySQL database - calculating some metrics (values)

[Ricmmf]

Hi,

 

i'm developing a BI academic work over Prestashop mysql database. I would like if someone could confirm this logic in SQL Server ( to develop the datawarehouse):

- to calculate the number of connections by day i should use table ps_connections and count records, filtering by each "date_add"

- to calculate the number of visits by day (being a visit a computer with some internet address, independently of being a registered customer or a visit), i should use table ps_connections and count distinct "ip_address", filtering by each "date_add"

- to calculate the number of clients that have visited the store by day, i must "go" (through "id_guest"...) from ps_connections to ps_guest, and count the records with "id_customer" diferent than 0 (zero), filtering by each "date_add"

- to calculate the number of rejections by day, and assuming that a rejection is a user that enters one single page and leaves the site, i must "go" from ps_connections to ps_connections_page (trough "id_connections"...) and count if the number of records is equal to 1.

 

Kind regards