-- lag
select x.id, x.id_lag
from (
SELECT id,
lag(id, 1) over (order by id) as id_lag
FROM test) x;
select x.id, x.lag_diff
from (
SELECT id,
id - lag(id, 1) over (order by id) as lag_diff
FROM test) x
where x.lag_diff > 1;
-- lead
select x.id, x.id_lead
from (
SELECT id,
lead(id, 1) over (order by id) as id_lead
FROM test) x;
select x.id, x.lead_diff
from (
SELECT id,
lead(id, 1) over (order by id) - id as lead_diff
FROM test) x
where x.lead_diff > 1;
SELECT Users.UserID, Users.ManagerId
FROM Users
INNER JOIN Wallets ON Users.UserID = Wallets.UserID
INNER JOIN WalletSections ON Wallets.WalletID = WalletSections.WalletID
INNER JOIN Transactions ON WalletSections.WalletSectionID = Transactions.WalletSectionID
GROUP BY Users.UserID, Users.ManagerId
HAVING (MAX(Transactions.CreationDate) BETWEEN '2018-10-01 00:00:00' AND '2018-10-31 23:59:59')и в деталях данной транзакции прописано значение Users.ManagerId.
SELECT Users.UserID
FROM Users
INNER JOIN Wallets ON Users.UserID = Wallets.UserID
INNER JOIN WalletSections ON Wallets.WalletID = WalletSections.WalletID
INNER JOIN Transactions ON WalletSections.WalletSectionID = Transactions.WalletSectionID
WHERE (Transactions.Detail = Users.ManagerId)
GROUP BY Users.UserID
HAVING (MAX(Transactions.CreationDate) BETWEEN '2018-10-01 00:00:00' AND '2018-10-31 23:59:59')SELECT Users.ManagerId, SUM(WalletSections.Balance)
FROM Users
INNER JOIN Wallets ON Users.UserID = Wallets.UserID
INNER JOIN WalletSections ON Wallets.WalletID = WalletSections.WalletID
WHERE (WalletSections.Currency = 2)
GROUP BY Users.ManagerId
HAVING SUM(WalletSections.Balance) > 10000
SELECT Users.UserID
FROM Users
INNER JOIN Wallets ON Users.UserID = Wallets.UserID
INNER JOIN WalletSections ON Wallets.WalletID = WalletSections.WalletID
INNER JOIN Transactions ON WalletSections.WalletSectionID = Transactions.WalletSectionID
WHERE (WalletSections.Currency = 1)
AND (Transactions.CreationDate BETWEEN Users.RegistrationDate AND DATEADD(yy, 1, Users.RegistrationDate))
GROUP BY Users.UserID
HAVING (COUNT(*) > 30)