-- HIVE SCRIPT FOR HABIT TRACKER
 
-- STEP 1: DROP EXISTING TABLES IF THEY ALREADY EXIST
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS habits;
DROP TABLE IF EXISTS habit_logs;
 
-- STEP 2: CREATE TABLES FOR USERS, HABITS, AND HABIT LOGS
 
-- Create users table to store user information
CREATE TABLE users (
    user_id INT,
    name STRING,
    email STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
 
-- Create habits table to store habit details for each user
CREATE TABLE habits (
    habit_id INT,
    user_id INT,
    habit_name STRING,
    description STRING,
    goal STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
 
-- Create habit_logs table to track daily habit completion
CREATE TABLE habit_logs (
    log_id INT,
    habit_id INT,
    user_id INT,
    date STRING,  -- format YYYY-MM-DD
    status STRING -- "completed" or "missed"
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
 
-- STEP 3: INSERT SAMPLE DATA INTO THE TABLES
 
-- Insert sample data for users
INSERT INTO users VALUES (1, 'Alice', 'alice@example.com');
INSERT INTO users VALUES (2, 'Bob', 'bob@example.com');
 
-- Insert sample data for habits
INSERT INTO habits VALUES (1, 1, 'Exercise', 'Daily exercise routine', '30 days');
INSERT INTO habits VALUES (2, 1, 'Read', 'Read a book for 15 minutes', '15 days');
INSERT INTO habits VALUES (3, 2, 'Meditate', '10 minutes of meditation', '20 days');
 
-- Insert sample data for habit logs (dates are in YYYY-MM-DD format)
INSERT INTO habit_logs VALUES (1, 1, 1, '2024-10-01', 'completed');
INSERT INTO habit_logs VALUES (2, 1, 1, '2024-10-02', 'completed');
INSERT INTO habit_logs VALUES (3, 1, 1, '2024-10-03', 'missed');
INSERT INTO habit_logs VALUES (4, 2, 1, '2024-10-01', 'completed');
INSERT INTO habit_logs VALUES (5, 3, 2, '2024-10-01', 'completed');
 
-- STEP 4: QUERIES FOR HABIT TRACKER REPORTING
 
-- QUERY 1: Retrieve All Habits for a User
-- This query fetches all habits for a specified user by name.
SELECT 
    h.habit_name,
    h.description,
    h.goal
FROM 
    habits h
JOIN 
    users u ON h.user_id = u.user_id
WHERE 
    u.name = 'Alice';
 
-- QUERY 2: Check Daily Progress on a Specific Habit
-- This query retrieves completion records for a specific habit by habit_id.
SELECT 
    date,
    status
FROM 
    habit_logs
WHERE 
    habit_id = 1  -- Replace with the habit_id you want to track
ORDER BY 
    date;
 
-- QUERY 3: Generate Habit Completion Rate for a User
-- This query calculates the completion rate (in percentage) for each habit of a user.
SELECT 
    h.habit_name,
    COUNT(CASE WHEN hl.status = 'completed' THEN 1 END) * 100.0 / COUNT(*) AS completion_rate
FROM 
    habit_logs hl
JOIN 
    habits h ON hl.habit_id = h.habit_id
WHERE 
    hl.user_id = 1  -- Replace with specific user_id
GROUP BY 
    h.habit_name;
 
-- END OF SCRIPT
 

LS0gSElWRSBTQ1JJUFQgRk9SIEhBQklUIFRSQUNLRVIKCi0tIFNURVAgMTogRFJPUCBFWElTVElORyBUQUJMRVMgSUYgVEhFWSBBTFJFQURZIEVYSVNUCkRST1AgVEFCTEUgSUYgRVhJU1RTIHVzZXJzOwpEUk9QIFRBQkxFIElGIEVYSVNUUyBoYWJpdHM7CkRST1AgVEFCTEUgSUYgRVhJU1RTIGhhYml0X2xvZ3M7CgotLSBTVEVQIDI6IENSRUFURSBUQUJMRVMgRk9SIFVTRVJTLCBIQUJJVFMsIEFORCBIQUJJVCBMT0dTCgotLSBDcmVhdGUgdXNlcnMgdGFibGUgdG8gc3RvcmUgdXNlciBpbmZvcm1hdGlvbgpDUkVBVEUgVEFCTEUgdXNlcnMgKAogICAgdXNlcl9pZCBJTlQsCiAgICBuYW1lIFNUUklORywKICAgIGVtYWlsIFNUUklORwopClJPVyBGT1JNQVQgREVMSU1JVEVECkZJRUxEUyBURVJNSU5BVEVEIEJZICcsJwpTVE9SRUQgQVMgVEVYVEZJTEU7CgotLSBDcmVhdGUgaGFiaXRzIHRhYmxlIHRvIHN0b3JlIGhhYml0IGRldGFpbHMgZm9yIGVhY2ggdXNlcgpDUkVBVEUgVEFCTEUgaGFiaXRzICgKICAgIGhhYml0X2lkIElOVCwKICAgIHVzZXJfaWQgSU5ULAogICAgaGFiaXRfbmFtZSBTVFJJTkcsCiAgICBkZXNjcmlwdGlvbiBTVFJJTkcsCiAgICBnb2FsIFNUUklORwopClJPVyBGT1JNQVQgREVMSU1JVEVECkZJRUxEUyBURVJNSU5BVEVEIEJZICcsJwpTVE9SRUQgQVMgVEVYVEZJTEU7CgotLSBDcmVhdGUgaGFiaXRfbG9ncyB0YWJsZSB0byB0cmFjayBkYWlseSBoYWJpdCBjb21wbGV0aW9uCkNSRUFURSBUQUJMRSBoYWJpdF9sb2dzICgKICAgIGxvZ19pZCBJTlQsCiAgICBoYWJpdF9pZCBJTlQsCiAgICB1c2VyX2lkIElOVCwKICAgIGRhdGUgU1RSSU5HLCAgLS0gZm9ybWF0IFlZWVktTU0tREQKICAgIHN0YXR1cyBTVFJJTkcgLS0gImNvbXBsZXRlZCIgb3IgIm1pc3NlZCIKKQpST1cgRk9STUFUIERFTElNSVRFRApGSUVMRFMgVEVSTUlOQVRFRCBCWSAnLCcKU1RPUkVEIEFTIFRFWFRGSUxFOwoKLS0gU1RFUCAzOiBJTlNFUlQgU0FNUExFIERBVEEgSU5UTyBUSEUgVEFCTEVTCgotLSBJbnNlcnQgc2FtcGxlIGRhdGEgZm9yIHVzZXJzCklOU0VSVCBJTlRPIHVzZXJzIFZBTFVFUyAoMSwgJ0FsaWNlJywgJ2FsaWNlQGV4YW1wbGUuY29tJyk7CklOU0VSVCBJTlRPIHVzZXJzIFZBTFVFUyAoMiwgJ0JvYicsICdib2JAZXhhbXBsZS5jb20nKTsKCi0tIEluc2VydCBzYW1wbGUgZGF0YSBmb3IgaGFiaXRzCklOU0VSVCBJTlRPIGhhYml0cyBWQUxVRVMgKDEsIDEsICdFeGVyY2lzZScsICdEYWlseSBleGVyY2lzZSByb3V0aW5lJywgJzMwIGRheXMnKTsKSU5TRVJUIElOVE8gaGFiaXRzIFZBTFVFUyAoMiwgMSwgJ1JlYWQnLCAnUmVhZCBhIGJvb2sgZm9yIDE1IG1pbnV0ZXMnLCAnMTUgZGF5cycpOwpJTlNFUlQgSU5UTyBoYWJpdHMgVkFMVUVTICgzLCAyLCAnTWVkaXRhdGUnLCAnMTAgbWludXRlcyBvZiBtZWRpdGF0aW9uJywgJzIwIGRheXMnKTsKCi0tIEluc2VydCBzYW1wbGUgZGF0YSBmb3IgaGFiaXQgbG9ncyAoZGF0ZXMgYXJlIGluIFlZWVktTU0tREQgZm9ybWF0KQpJTlNFUlQgSU5UTyBoYWJpdF9sb2dzIFZBTFVFUyAoMSwgMSwgMSwgJzIwMjQtMTAtMDEnLCAnY29tcGxldGVkJyk7CklOU0VSVCBJTlRPIGhhYml0X2xvZ3MgVkFMVUVTICgyLCAxLCAxLCAnMjAyNC0xMC0wMicsICdjb21wbGV0ZWQnKTsKSU5TRVJUIElOVE8gaGFiaXRfbG9ncyBWQUxVRVMgKDMsIDEsIDEsICcyMDI0LTEwLTAzJywgJ21pc3NlZCcpOwpJTlNFUlQgSU5UTyBoYWJpdF9sb2dzIFZBTFVFUyAoNCwgMiwgMSwgJzIwMjQtMTAtMDEnLCAnY29tcGxldGVkJyk7CklOU0VSVCBJTlRPIGhhYml0X2xvZ3MgVkFMVUVTICg1LCAzLCAyLCAnMjAyNC0xMC0wMScsICdjb21wbGV0ZWQnKTsKCi0tIFNURVAgNDogUVVFUklFUyBGT1IgSEFCSVQgVFJBQ0tFUiBSRVBPUlRJTkcKCi0tIFFVRVJZIDE6IFJldHJpZXZlIEFsbCBIYWJpdHMgZm9yIGEgVXNlcgotLSBUaGlzIHF1ZXJ5IGZldGNoZXMgYWxsIGhhYml0cyBmb3IgYSBzcGVjaWZpZWQgdXNlciBieSBuYW1lLgpTRUxFQ1QgCiAgICBoLmhhYml0X25hbWUsCiAgICBoLmRlc2NyaXB0aW9uLAogICAgaC5nb2FsCkZST00gCiAgICBoYWJpdHMgaApKT0lOIAogICAgdXNlcnMgdSBPTiBoLnVzZXJfaWQgPSB1LnVzZXJfaWQKV0hFUkUgCiAgICB1Lm5hbWUgPSAnQWxpY2UnOwoKLS0gUVVFUlkgMjogQ2hlY2sgRGFpbHkgUHJvZ3Jlc3Mgb24gYSBTcGVjaWZpYyBIYWJpdAotLSBUaGlzIHF1ZXJ5IHJldHJpZXZlcyBjb21wbGV0aW9uIHJlY29yZHMgZm9yIGEgc3BlY2lmaWMgaGFiaXQgYnkgaGFiaXRfaWQuClNFTEVDVCAKICAgIGRhdGUsCiAgICBzdGF0dXMKRlJPTSAKICAgIGhhYml0X2xvZ3MKV0hFUkUgCiAgICBoYWJpdF9pZCA9IDEgIC0tIFJlcGxhY2Ugd2l0aCB0aGUgaGFiaXRfaWQgeW91IHdhbnQgdG8gdHJhY2sKT1JERVIgQlkgCiAgICBkYXRlOwoKLS0gUVVFUlkgMzogR2VuZXJhdGUgSGFiaXQgQ29tcGxldGlvbiBSYXRlIGZvciBhIFVzZXIKLS0gVGhpcyBxdWVyeSBjYWxjdWxhdGVzIHRoZSBjb21wbGV0aW9uIHJhdGUgKGluIHBlcmNlbnRhZ2UpIGZvciBlYWNoIGhhYml0IG9mIGEgdXNlci4KU0VMRUNUIAogICAgaC5oYWJpdF9uYW1lLAogICAgQ09VTlQoQ0FTRSBXSEVOIGhsLnN0YXR1cyA9ICdjb21wbGV0ZWQnIFRIRU4gMSBFTkQpICogMTAwLjAgLyBDT1VOVCgqKSBBUyBjb21wbGV0aW9uX3JhdGUKRlJPTSAKICAgIGhhYml0X2xvZ3MgaGwKSk9JTiAKICAgIGhhYml0cyBoIE9OIGhsLmhhYml0X2lkID0gaC5oYWJpdF9pZApXSEVSRSAKICAgIGhsLnVzZXJfaWQgPSAxICAtLSBSZXBsYWNlIHdpdGggc3BlY2lmaWMgdXNlcl9pZApHUk9VUCBCWSAKICAgIGguaGFiaXRfbmFtZTsKCi0tIEVORCBPRiBTQ1JJUFQK