Analytics SQLs

Jake authory uzivatel sleduje

SELECT uzivatele.name
FROM users_user AS uzivatele
JOIN articles_subscriptiontoauthor AS autori ON autori.author_id = uzivatele.id
WHERE autori.user_id = <id uzivatele>

Jake noviny uzivatel sleduje

SELECT newspaper.title
FROM articles_newspaper AS newspaper
JOIN articles_subscription AS subscription ON subscription.newspaper_id = newspaper.id
WHERE subscription.user_id = <id uzivatele>

Kolik maji jednotlivy aktivni uzivatele novin

SELECT COUNT(newspaper.id), users_user.email, users_user.name 
FROM users_user 
LEFT JOIN articles_subscription AS subscription ON subscription.user_id = users_user.id 
LEFT JOIN articles_newspaper AS newspaper ON subscription.newspaper_id = newspaper.id 
GROUP BY subscription.user_id

Kolik maji aktivni uzivatele autoru

SELECT COUNT(users.id), users.email, users.name
FROM users_user AS users
LEFT JOIN articles_subscriptiontoauthor AS subscription ON subscription.user_id = users.id
LEFT JOIN users_user AS authori ON subscription.author_id = authori.id
GROUP BY subscription.user_id