Senior
1. Display unique combinations of user and product id for all purchases made by users before they were banned. Sort first by user name, then by SKU. 2. Find users who made purchases totaling more than 5000 rubles. Display their id, first name, last name, and total purchase amount.
sobes.tech AI
Answer from AI
To solve the database task, you can use SQL queries, and in Go — execute them through standard libraries.
- Unique combinations of user and product id for purchases made before the ban, sorted by name and SKU:
SELECT DISTINCT u.firstname, u.lastname, p.sku
FROM purchase p
JOIN user u ON p.user_id = u.id
LEFT JOIN ban_list b ON p.user_id = b.user_id
WHERE b.date_from IS NULL OR p.date < b.date_from
ORDER BY u.firstname, p.sku;
- Users with total purchases over 5000:
SELECT u.id, u.firstname, u.lastname, SUM(p.price) AS total_spent
FROM purchase p
JOIN user u ON p.user_id = u.id
GROUP BY u.id, u.firstname, u.lastname
HAVING SUM(p.price) > 5000;
In Go, you can execute these queries using database/sql and process the results. Example for the second query:
rows, err := db.Query(`
SELECT u.id, u.firstname, u.lastname, SUM(p.price) AS total_spent
FROM purchase p
JOIN user u ON p.user_id = u.id
GROUP BY u.id, u.firstname, u.lastname
HAVING SUM(p.price) > 5000
`)
if err != nil {
log.Fatal(err)
}
type UserSpending struct {
ID int
FirstName string
LastName string
Total float64
}
var results []UserSpending
for rows.Next() {
var u UserSpending
if err := rows.Scan(&u.ID, &u.FirstName, &u.LastName, &u.Total); err != nil {
log.Fatal(err)
}
results = append(results, u)
}
// results now contains users with total purchases > 5000
Thus, the main logic is to correctly form SQL queries with conditions on ban date and aggregation of sums.