Stworzyłem kilka zapytań które wstępnie sprawdziłem i działają ale będę wdzięczny za jakieś spostrzeżenia, ewentualnie rady w poprawieniu.
Mam dwie tabelki pierwsza to 'users' z kolumnami
id - (oczywiście primary key autoincrement), name, email
druga tabela to 'invoices'z kolumnami
user_id, amount, created_at
Przykładowy wiersz z pierwszej tabeli wygląda tak ; 16 Esther Barton esther.white@wiegand.info
Przykładowy wiersz z drugiej tabeli wygląda tak ; 2180 12.61 2015-06-16
A poniżej co chcę osiągnąć i zapytania;
ile każdego dnia było transakcji
SELECT created_at, COUNT(*) AS countValue FROM invoices GROUP BY created_at ORDER BY created_at DESC
ile każdego dnia było unikalnych użytkowników przeprowadzających transakcję
SELECT COUNT(DISTINCT user_id) AS number, created_at FROM invoices GROUP BY created_at ORDER BY created_at DESC
ile było użytkowników, w każdej domenie mailowej
SELECT DISTINCT (SUBSTRING_INDEX(email, '.', -1)) AS emailSlice, COUNT(id) AS countUsers FROM users GROUP BY (SELECT DISTINCT (SUBSTRING_INDEX(email, '.', -1)))
ile transakcji miał każdy użytkownik
SELECT DISTINCT invoices.user_id, users.name, COUNT(created_at) AS countDate FROM invoices INNER JOIN users ON invoices.user_id = users.id GROUP BY user_id ORDER BY user_id
wszyscy użytkownicy, którzy mają więcej niż 3 transakcje
SELECT invoices.user_id, users.name, COUNT(*) AS countAmount FROM invoices INNER JOIN users ON invoices.user_id = users.id GROUP BY invoices.user_id HAVING COUNT(*) > 3
jaka była średnia wartość transakcji i odchylenie standardowe z ostatnich 7 dni, w których występują transakcje
SELECT ROUND(AVG(total),2) AS avgTotal, ROUND(STDDEV(total),2) AS devTotal FROM (SELECT ROUND(SUM(amount),2) AS total FROM invoices GROUP BY created_at ORDER BY created_at DESC LIMIT 7) AS fromList