Average Time of Process per Machine solution and explanation

# Python solution using a dictionary to track start and end times
from collections import defaultdict

activity_data = [
    (0, 0, 'start', 0.712),
    (0, 0, 'end', 1.520),
    (0, 1, 'start', 3.140),
    (0, 1, 'end', 4.120),
    (1, 0, 'start', 0.550),
    (1, 0, 'end', 1.550),
    (1, 1, 'start', 0.430),
    (1, 1, 'end', 1.420),
    (2, 0, 'start', 4.100),
    (2, 0, 'end', 4.512),
    (2, 1, 'start', 2.500),
    (2, 1, 'end', 5.000)
]

machine_times = defaultdict(list)

for machine_id, process_id, activity_type, timestamp in activity_data:
    machine_times[(machine_id, process_id)].append(timestamp)

avg_times = defaultdict(float)
process_count = defaultdict(int)

for (machine_id, process_id), times in machine_times.items():
    if times[0] < times[1]:
        process_time = times[1] - times[0]
    else:
        process_time = times[0] - times[1]
    avg_times[machine_id] += process_time
    process_count[machine_id] += 1

result = []
for machine_id in avg_times:
    average_time = round(avg_times[machine_id] / process_count[machine_id], 3)
    result.append((machine_id, average_time))

# Output the result
def print_result(result):
    print("+------------+-----------------+")
    print("| machine_id | processing_time |")
    print("+------------+-----------------+")
    for machine_id, avg_time in result:
        print(f"| {machine_id}          | {avg_time:.3f}           |")
    print("+------------+-----------------+")

print_result(result)

// JavaScript solution using a map to track start and end times
const activityData = [
    {machine_id: 0, process_id: 0, activity_type: 'start', timestamp: 0.712},
    {machine_id: 0, process_id: 0, activity_type: 'end', timestamp: 1.520},
    {machine_id: 0, process_id: 1, activity_type: 'start', timestamp: 3.140},
    {machine_id: 0, process_id: 1, activity_type: 'end', timestamp: 4.120},
    {machine_id: 1, process_id: 0, activity_type: 'start', timestamp: 0.550},
    {machine_id: 1, process_id: 0, activity_type: 'end', timestamp: 1.550},
    {machine_id: 1, process_id: 1, activity_type: 'start', timestamp: 0.430},
    {machine_id: 1, process_id: 1, activity_type: 'end', timestamp: 1.420},
    {machine_id: 2, process_id: 0, activity_type: 'start', timestamp: 4.100},
    {machine_id: 2, process_id: 0, activity_type: 'end', timestamp: 4.512},
    {machine_id: 2, process_id: 1, activity_type: 'start', timestamp: 2.500},
    {machine_id: 2, process_id: 1, activity_type: 'end', timestamp: 5.000}
];

const machineTimes = new Map();

for (const {machine_id, process_id, activity_type, timestamp} of activityData) {
    const key = `${machine_id},${process_id}`;
    if (!machineTimes.has(key)) machineTimes.set(key, []);
    machineTimes.get(key).push(timestamp);
}

const avgTimes = new Map();
const processCount = new Map();

for (const [key, times] of machineTimes.entries()) {
    const [machine_id] = key.split(',').map(Number);
    const processTime = Math.abs(times[1] - times[0]);
    if (!avgTimes.has(machine_id)) avgTimes.set(machine_id, 0);
    if (!processCount.has(machine_id)) processCount.set(machine_id, 0);
    avgTimes.set(machine_id, avgTimes.get(machine_id) + processTime);
    processCount.set(machine_id, processCount.get(machine_id) + 1);
}

const result = [];
for (const [machine_id, totalTime] of avgTimes) {
    const averageTime = parseFloat((totalTime / processCount.get(machine_id)).toFixed(3));
    result.push({ machine_id, processing_time: averageTime });
}

console.table(result);

// Java solution with sorting and linear scan
import java.util.*;

class Activity {
    int machine_id;
    int process_id;
    String activity_type;
    double timestamp;
    public Activity(int m_id, int p_id, String a_type, double time) {
        this.machine_id = m_id;
        this.process_id = p_id;
        this.activity_type = a_type;
        this.timestamp = time;
    }
}

public class AverageProcessingTime {
    public static void main(String[] args) {
        List<Activity> activities = new ArrayList<>();
        activities.add(new Activity(0, 0, "start", 0.712));
        activities.add(new Activity(0, 0, "end", 1.520));
        activities.add(new Activity(0, 1, "start", 3.140));
        activities.add(new Activity(0, 1, "end", 4.120));
        activities.add(new Activity(1, 0, "start", 0.550));
        activities.add(new Activity(1, 0, "end", 1.550));
        activities.add(new Activity(1, 1, "start", 0.430));
        activities.add(new Activity(1, 1, "end", 1.420));
        activities.add(new Activity(2, 0, "start", 4.100));
        activities.add(new Activity(2, 0, "end", 4.512));
        activities.add(new Activity(2, 1, "start", 2.500));
        activities.add(new Activity(2, 1, "end", 5.000));

        Map<Integer, Double> totalTimes = new HashMap<>();
        Map<Integer, Integer> processCounts = new HashMap<>();

        // Sort the data by machine_id and process_id
        activities.sort((a, b) -> {
            if (a.machine_id != b.machine_id) return a.machine_id - b.machine_id;
            return a.process_id - b.process_id;
        });

        for (int i = 0; i < activities.size(); i += 2) {
            Activity start = activities.get(i);
            Activity end = activities.get(i + 1);
            double processTime = end.timestamp - start.timestamp;
            totalTimes.put(start.machine_id, totalTimes.getOrDefault(start.machine_id, 0.0) + processTime);
            processCounts.put(start.machine_id, processCounts.getOrDefault(start.machine_id, 0) + 1);
        }

        // Calculate average process times
        List<String> results = new ArrayList<>();
        for (Map.Entry<Integer, Double> entry : totalTimes.entrySet()) {
            int machine_id = entry.getKey();
            double averageTime = entry.getValue() / processCounts.get(machine_id);
            results.add(String.format("Machine ID: %d, Processing Time: %.3f", machine_id, averageTime));
        }

        // Output results
        results.forEach(System.out::println);
    }
}

// C++ solution using sorting and linear scan
#include <iostream>
#include <vector>
#include <unordered_map>
#include <algorithm>
#include <iomanip>

struct Activity {
    int machine_id;
    int process_id;
    std::string activity_type;
    double timestamp;
};

int main() {
    std::vector<Activity> activities = {
        {0, 0, "start", 0.712},
        {0, 0, "end", 1.520},
        {0, 1, "start", 3.140},
        {0, 1, "end", 4.120},
        {1, 0, "start", 0.550},
        {1, 0, "end", 1.550},
        {1, 1, "start", 0.430},
        {1, 1, "end", 1.420},
        {2, 0, "start", 4.100},
        {2, 0, "end", 4.512},
        {2, 1, "start", 2.500},
        {2, 1, "end", 5.000}
    };

    std::unordered_map<int, double> totalTimes;
    std::unordered_map<int, int> processCounts;

    // Sort activities based on machine_id and process_id
    std::sort(activities.begin(), activities.end(), [](const Activity &a, const Activity &b) {
        if (a.machine_id != b.machine_id) return a.machine_id < b.machine_id;
        return a.process_id < b.process_id;
    });

    for (size_t i = 0; i < activities.size(); i += 2) {
        const Activity &start = activities[i];
        const Activity &end = activities[i + 1];
        double processTime = end.timestamp - start.timestamp;
        totalTimes[start.machine_id] += processTime;
        processCounts[start.machine_id]++;
    }

    // Output average processing time per machine
    std::cout << "+------------+-----------------+" << std::endl;
    std::cout << "| machine_id | processing_time |" << std::endl;
    std::cout << "+------------+-----------------+" << std::endl;
    for (const auto &entry : totalTimes) {
        int machine_id = entry.first;
        double averageTime = entry.second / processCounts[machine_id];
        std::cout << "| " << machine_id << "          | " << std::fixed << std::setprecision(3) << averageTime << "           |" << std::endl;
    }
    std::cout << "+------------+-----------------+" << std::endl;

    return 0;
}

# Write your MySQL query statement below
SELECT
    machine_id,
    ROUND(
        AVG(
            CASE
                WHEN activity_type = 'start' THEN -timestamp
                ELSE timestamp
            END
        ) * 2,
        3
    ) AS processing_time
FROM Activity
GROUP BY 1;

# Write your MySQL query statement below
SELECT
    machine_id,
    ROUND(AVG(IF(activity_type = 'start', -1, 1) * timestamp) * 2, 3) AS processing_time
FROM Activity
GROUP BY 1;