Sponsored
Sponsored
This approach utilizes SQL LEFT JOIN to find all visits that do not have corresponding transactions and GROUP BY to count the occurrences.
Time Complexity: O(N + M), where N is the number of rows in the Visits table and M is the number of rows in the Transactions table.
Space Complexity: O(K), where K is the number of unique customer_id entries in the result set.
1// JavaScript using node-sqlite3
2const sqlite3 = require('sqlite3').verbose();
3
4let db = new sqlite3.Database('./your_database.db', (err) => {
5 if (err) {
6 console.error(err.message);
7 }
8 console.log('Connected to the SQlite database.');
9});
10
11let sql = `
12SELECT customer_id, COUNT(visit_id) AS count_no_trans
13FROM Visits
14LEFT JOIN Transactions ON Visits.visit_id = Transactions.visit_id
15WHERE Transactions.transaction_id IS NULL
16GROUP BY customer_id;`;
17
18// Execute SQL query
19let customerVisits = db.all(sql, [], (err, rows) => {
20 if (err) {
21 throw err;
22 }
23 rows.forEach((row) => {
24 console.log(`${row.customer_id} - ${row.count_no_trans}`);
25 });
26});
27
28db.close((err) => {
29 if (err) {
30 console.error(err.message);
31 }
32 console.log('Close the database connection.');
33});This JavaScript solution uses the sqlite3 library for Node.js to open a database connection, execute a query, and process the results. The query uses SQL LEFT JOIN and COUNT to list customers who visited but didn't make transactions.
This approach uses a subquery with NOT EXISTS to find all visits by customers that do not appear in the Transactions table.
Time Complexity: O(N * M), where N is the number of rows in the Visits table and M is the number of rows in the Transactions table.
Space Complexity: O(K), where K is the number of unique customer_id entries in the result set.
This Java example implements JDBC to run a SQL query with a subquery. It employs the NOT EXISTS clause to search for visits in the database that aren't part of any transaction, allowing us to track customer visits without purchases.